Saturday 1 October 2016

c# - Returning IEnumerable vs. IQueryable




What is the difference between returning IQueryable vs. IEnumerable?



IQueryable custs = from c in db.Customers
where c.City == ""
select c;

IEnumerable custs = from c in db.Customers
where c.City == ""
select c;


Will both be deferred execution and when should one be preferred over the other?


Answer



Yes, both will give you deferred execution.



The difference is that IQueryable is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable, that query will be executed in the database, if possible.



For the IEnumerable case, it will be LINQ-to-object, meaning that all objects matching the original query will have to be loaded into memory from the database.



In code:



IQueryable custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);


That code will execute SQL to only select gold customers. The following code, on the other hand, will execute the original query in the database, then filtering out the non-gold customers in the memory:



IEnumerable custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);


This is quite an important difference, and working on IQueryable can in many cases save you from returning too many rows from the database. Another prime example is doing paging: If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable will cause all of your rows to be loaded in memory.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...