Returning IEnumerable T vs IQueryable T

Question

What is the difference between returning IQueryable lt T gt  vs  IEnumerable lt T gt   when should one be preferred over the other  IQueryable lt Customer gt  custs   from c in db Customers where c City     quot  lt City gt  quot  select c   IEnumerable lt Customer gt  custs   from c in db Customers where c City     quot  lt City gt  quot  select c   Will both be deferred execution and when should one be preferred over the other

User · Answer

In addition to the above  it s interesting to note that you can get exceptions if you use IQueryable instead of IEnumerable   The following works fine if products is an IEnumerable   products Skip -4     However if products is an IQueryable and it s trying to access records from a DB table  then you ll get this error      The offset specified in a OFFSET clause may not be negative    This is because the following query was constructed   SELECT  p   ProductId  FROM  Products  AS  p  ORDER BY  SELECT 1  OFFSET    p 0 ROWS   and OFFSET can t have a negative value

User · Answer

Yes  both use deferred execution  Let s illustrate the difference using the SQL Server profiler      When we run the following code   MarketDevEntities db   new MarketDevEntities     IEnumerable lt WebLog gt  first   db WebLogs  var second   first Where c   gt  c DurationSeconds  gt  10   var third   second Where c   gt  c WebLogID  gt  100   var result   third Where c   gt  c EmailAddress Length  gt  11    Console Write result First   UserName     In SQL Server profiler we find a command equal to    SELECT   FROM  dbo   WebLog     It approximately takes 90 seconds to run that block of code against a WebLog table which has 1 million records   So  all table records are loaded into memory as objects  and then with each  Where   it will be another filter in memory against these objects   When we use IQueryable instead of IEnumerable in the above example  second line    In SQL Server profiler we find a command equal to    SELECT TOP 1   FROM  dbo   WebLog  WHERE  DurationSeconds   gt  10 AND  WebLogID   gt  100 AND LEN  EmailAddress    gt  11    It approximately takes four seconds to run this block of code using IQueryable   IQueryable has a property called Expression which stores a tree expression which starts being created when we used the result in our example  which is called deferred execution   and at the end this expression will be converted to an SQL query to run on the database engine

User · Answer

I would like to clarify a few things due to seemingly conflicting responses  mostly surrounding IEnumerable     1  IQueryable extends the IEnumerable interface    You can send an IQueryable to something which expects IEnumerable without error     2  Both IQueryable and IEnumerable LINQ attempt lazy loading when iterating over the result set   Note that implementation can be seen in interface extension methods for each type    In other words  IEnumerables are not exclusively  in-memory    IQueryables are not always executed on the database   IEnumerable must load things into memory  once retrieved  possibly lazily  because it has no abstract data provider   IQueryables rely on an abstract provider  like LINQ-to-SQL   although this could also be the  NET in-memory provider   Sample use case   a  Retrieve list of records as IQueryable from EF context    No records are in-memory     b  Pass the IQueryable to a view whose model is IEnumerable    Valid  IQueryable extends IEnumerable     c  Iterate over and access the data set s records  child entities and properties from the view    May cause exceptions    Possible Issues   1  The IEnumerable attempts lazy loading and your data context is expired   Exception thrown because provider is no longer available    2  Entity Framework entity proxies are enabled  the default   and you attempt to access a related  virtual  object with an expired data context   Same as  1     3  Multiple Active Result Sets  MARS    If you are iterating over the IEnumerable in a foreach  var record in resultSet   block and simultaneously attempt to access record childEntity childProperty  you may end up with MARS due to lazy loading of both the data set and the relational entity   This will cause an exception if it is not enabled in your connection string   Solution   I have found that enabling MARS in the connection string works unreliably   I suggest you avoid MARS unless it is well-understood and explicitly desired    Execute the query and store results by invoking resultList   resultSet ToList    This seems to be the most straightforward way of ensuring your entities are in-memory   In cases where the you are accessing related entities  you may still require a data context   Either that  or you can disable entity proxies and explicitly Include related entities from your DbSet

User · Answer

The top answer is good but it doesn t mention expression trees which explain  how  the two interfaces differ  Basically  there are two identical sets of LINQ extensions  Where    Sum    Count    FirstOrDefault    etc all have two versions  one that accepts functions and one that accepts expressions    The IEnumerable version signature is  Where Func lt Customer  bool gt  predicate  The IQueryable version signature is  Where Expression lt Func lt Customer  bool gt  gt  predicate    You ve probably been using both of those without realizing it because both are called using identical syntax   e g  Where x   gt  x City      lt City gt    works on both IEnumerable and IQueryable   When using Where   on an IEnumerable collection  the compiler passes a compiled function to Where   When using Where   on an IQueryable collection  the compiler passes an expression tree to Where    An expression tree is like the reflection system but for code  The compiler converts your code into a data structure that describes what your code does in a format that s easily digestible    Why bother with this expression tree thing  I just want Where   to filter my data  The main reason is that both the EF and Linq2SQL ORMs can convert expression trees directly into SQL where your code will execute much faster   Oh  that sounds like a free performance boost  should I use AsQueryable   all over the place in that case   No  IQueryable is only useful if the underlying data provider can do something with it  Converting something like a regular List to IQueryable will not give you any benefit

User · Answer

In general you want to preserve the original static type of the query until it matters   For this reason  you can define your variable as  var  instead of either IQueryable lt  gt  or IEnumerable lt  gt  and you will know that you are not changing the type   If you start out with an IQueryable lt  gt   you typically want to keep it as an IQueryable lt  gt  until there is some compelling reason to change it  The reason for this is that you want to give the query processor as much information as possible  For example  if you re only going to use 10 results  you ve called Take 10   then you want SQL Server to know about that so that it can optimize its query plans and send you only the data you ll use   A compelling reason to change the type from IQueryable lt  gt  to IEnumerable lt  gt  might be that you are calling some extension function that the implementation of IQueryable lt  gt  in your particular object either cannot handle or handles inefficiently  In that case  you might wish to convert the type to IEnumerable lt  gt   by assigning to a variable of type IEnumerable lt  gt  or by using the AsEnumerable extension method for example  so that the extension functions you call end up being the ones in the Enumerable class instead of the Queryable class

User · Answer

Yes  both will give you deferred execution   The difference is that IQueryable lt T gt  is the interface that allows LINQ-to-SQL  LINQ -to-anything really  to work  So if you further refine your query on an IQueryable lt T gt   that query will be executed in the database  if possible    For the IEnumerable lt T gt  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 lt Customer gt  custs           Later on    var goldCustomers   custs Where c   gt  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 lt Customer gt  custs           Later on    var goldCustomers   custs Where c   gt  c IsGold     This is quite an important difference  and working on IQueryable lt T gt  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 lt T gt  will cause all of your rows to be loaded in memory

User · Answer

There is a blog post with brief source code sample about how misuse of IEnumerable lt T gt  can dramatically impact LINQ query performance  Entity Framework  IQueryable vs  IEnumerable   If we dig deeper and look into the sources  we can see that there are obviously different extension methods are perfomed for IEnumerable lt T gt       Type  System Linq Enumerable    Assembly  System Core  Version 4 0 0 0  Culture neutral  PublicKeyToken b77a5c561934e089    Assembly location  C  Windows Microsoft NET Framework v4 0 30319 System Core dll public static class Enumerable       public static IEnumerable lt TSource gt  Where lt TSource gt           this IEnumerable lt TSource gt  source           Func lt TSource  bool gt  predicate                return  IEnumerable lt TSource gt                new Enumerable WhereEnumerableIterator lt TSource gt  source  predicate             and IQueryable lt T gt       Type  System Linq Queryable    Assembly  System Core  Version 4 0 0 0  Culture neutral  PublicKeyToken b77a5c561934e089    Assembly location  C  Windows Microsoft NET Framework v4 0 30319 System Core dll public static class Queryable       public static IQueryable lt TSource gt  Where lt TSource gt           this IQueryable lt TSource gt  source           Expression lt Func lt TSource  bool gt  gt  predicate                return source Provider CreateQuery lt TSource gt               Expression Call                  null                     MethodInfo  MethodBase GetCurrentMethod    MakeGenericMethod                      new Type     typeof TSource                           new Expression                              source Expression  Expression Quote predicate                 The first one returns enumerable iterator  and the second one creates query through the query provider  specified in IQueryable source

User · Answer

The main difference between    IEnumerable    and    IQueryable    is about where the filter logic is executed  One executes on the client side  in memory  and the other executes on the database  For example  we can consider an example where we have 10 000 records for a user in our database and let s say only 900 out which are active users  so in this case if we use    IEnumerable    then first it loads all 10 000 records in memory and then applies the IsActive filter on it which eventually returns the 900 active users  While on the other hand on the same case if we use    IQueryable    it will directly apply the IsActive filter on the database which directly from there will return the 900 active users

User · Answer

We can use both for the same way  and they are only different in the performance   IQueryable only executes against the database in an efficient way  It means that it creates an entire select query and only gets the related records   For example  we want to take the top 10 customers whose name start with    Nimal     In this case the select query will be generated as select top 10   from Customer where name like    Nimal       But if we used IEnumerable  the query would be like select   from Customer where name like    Nimal     and the top ten will be filtered at the C  coding level  it gets all the customer records from the database and passes them into C

User · Answer

In general terms I would recommend the following    Return IQueryable lt T gt  if you want to enable the developer using your method to refine the query you return before executing  Return IEnumerable if you want to transport a set of Objects to enumerate over     Imagine an IQueryable as that what it is - a  query  for data  which you can refine if you want to   An IEnumerable is a set of objects  which has already been received or was created  over which you can enumerate

User · Answer

Both will give you deferred execution  yes   As for which is preferred over the other  it depends on what your underlying datasource is   Returning an IEnumerable will automatically force the runtime to use LINQ to Objects to query your collection   Returning an IQueryable  which implements IEnumerable  by the way  provides the extra functionality to translate your query into something that might perform better on the underlying source  LINQ to SQL  LINQ to XML  etc

User · Answer

I recently ran into an issue with IEnumerable v  IQueryable  The algorithm being used first performed an IQueryable query to obtain a set of results  These were then passed to a foreach loop  with the items instantiated as an Entity Framework  EF  class  This EF class was then used in the from clause of a Linq to Entity query  causing the result to be IEnumerable    I m fairly new to EF and Linq for Entities  so it took a while to figure out what the bottleneck was  Using MiniProfiling  I found the query and then converted all of the individual operations to a single IQueryable Linq for Entities query  The IEnumerable took 15 seconds and the IQueryable took 0 5 seconds to execute  There were three tables involved and  after reading this  I believe that the IEnumerable query was actually forming a three table cross-product and filtering the results   Try to use IQueryables as a rule-of-thumb and profile your work to make your changes measurable

User · Answer

In addition to first 2 really good answers  by driis  amp  by Jacob        IEnumerable   interface is in the System Collections namespace     The IEnumerable object represents a set of data in memory and can move on this data only forward  The query represented by the IEnumerable object is executed immediately and completely  so the application receives data quickly   When the query is executed  IEnumerable loads all the data  and if we need to filter it  the filtering itself is done on the client side      IQueryable interface is located in the System Linq namespace    The IQueryable object provides remote access to the database and allows you to navigate through the data either in a direct order from beginning to end  or in the reverse order  In the process of creating a query  the returned object is IQueryable  the query is optimized  As a result  less memory is consumed during its execution  less network bandwidth  but at the same time it can be processed slightly more slowly than a query that returns an IEnumerable object   What to choose   If you need the entire set of returned data  then it s better to use IEnumerable  which provides the maximum speed    If you DO NOT need the entire set of returned data  but only some filtered data  then it s better to use IQueryable

User · Answer

A lot has been said previously  but back to the roots  in a more technical way    IEnumerable is a collection of objects in memory that you can enumerate - an in-memory sequence that makes it possible to iterate through  makes it way easy for within foreach loop  though you can go with IEnumerator only   They reside in the memory as is  IQueryable  is an expression tree that will get translated into something else at some point with ability to enumerate over the final outcome  I guess this is what confuses most people    They obviously have different connotations   IQueryable represents an expression tree  a query  simply  that will be translated to something else by the underlying query provider as soon as release APIs are called  like LINQ aggregate functions  Sum  Count  etc   or ToList Array  Dictionary       And IQueryable objects also implement IEnumerable  IEnumerable lt T gt  so that if they represent a query the result of that query could be iterated  It means IQueryable don t have to be queries only  The right term is they are expression trees    Now how those expressions are executed and what they turn to is all up to so called query providers  expression executors we can think them of    In the Entity Framework world  which is that mystical underlying data source provider  or the query provider  IQueryable expressions are translated into native T-SQL queries  Nhibernate does similar things with them  You can write your own one following the concepts pretty well described in LINQ  Building an IQueryable Provider link  for example  and you might want to have a custom querying API for your product store provider service   So basically  IQueryable objects are getting constructed all the way long until we explicitly release them and tell the system to rewrite them into SQL or whatever and send down the execution chain for onward processing   As if to deferred execution it s a LINQ feature to hold up the expression tree scheme in the memory and send it into the execution only on demand  whenever certain APIs are called against the sequence  the same Count  ToList  etc     The proper usage of both heavily depends on the tasks you re facing for the specific case  For the well-known repository pattern I personally opt for returning IList  that is IEnumerable over Lists  indexers and the like   So it is my advice to use IQueryable only within repositories and IEnumerable anywhere else in the code  Not saying about the testability concerns that IQueryable breaks down and ruins the separation of concerns principle  If you return an expression from within repositories consumers may play with the persistence layer as they would wish   A little addition to the mess     from a discussion in the comments   None of them are objects in memory since they re not real types per se  they re markers of a type - if you want to go that deep  But it makes sense  and that s why even MSDN put it this way  to think of IEnumerables as in-memory collections whereas IQueryables as expression trees  The point is that the IQueryable interface inherits the IEnumerable interface so that if it represents a query  the results of that query can be enumerated  Enumeration causes the expression tree associated with an IQueryable object to be executed  So  in fact  you can t really call any IEnumerable member without having the object in the memory  It will get in there if you do  anyways  if it s not empty  IQueryables are just queries  not the data

[c#] Returning IEnumerable<T> vs. IQueryable<T>

Examples related to c#

Examples related to linq

Examples related to linq-to-sql

Examples related to ienumerable

Examples related to iqueryable