Problem
What is the difference between returning IQueryableT> and IEnumerableT>, and when should one be used instead of the other?
IQueryable<Customer> custs = from c in db.Customers
where c.City == "<City>"
select c;
IEnumerable<Customer> custs = from c in db.Customers
where c.City == "<City>"
select c;
Will both be put on hold, and when should one be chosen over the other?
Asked by stackoverflowuser
Solution #1
Yes, you will be granted deferred execution in both cases.
IQueryableT> is the interface that allows LINQ-to-SQL (or LINQ.-to-anything, for that matter) to work. If you refine your query further on an IQueryableT>, the query will be conducted in the database if it is possible.
It will be LINQ-to-object for the IEnumerableT> scenario, which means that all objects matching the original query will have to be loaded into memory from the database.
In code:
IQueryable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);
This code will run SQL to select only gold customers. In contrast, the following code will run the original query in the database before filtering out the non-gold customers in memory:
IEnumerable<Customer> 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. Doing paging is another great example: 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.
Answered by driis
Solution #2
The first solution is correct, but it omits expression trees, which describe “how” the two interfaces vary. There are two sets of LINQ extensions, which are basically the same. There are two versions of Where(), Sum(), Count(), FirstOrDefault(), and so on: one that accepts functions and one that accepts expressions.
You’ve most likely been using both of them without recognizing it because they have the same syntax:
Where(x => x.City == “City>”), for example, works on both IEnumerable and IQueryable objects.
What’s the point of this expression tree nonsense? I’m only looking for a way to use Where() to filter my data. The primary reason for this is that both the EF and Linq2SQL ORMs can convert expression trees directly into SQL, which allows your code to run significantly faster.
Oh, that sounds like a free performance improvement; should I start using AsQueryable() everywhere now? No, IQueryable is only helpful if it can be used by the underlying data provider. Converting an ordinary List to IQueryable provides no value.
Answered by Jacob
Solution #3
Deferred execution is used in both cases. Let’s use the SQL Server profiler to demonstrate the difference….
When the following code is executed:
MarketDevEntities db = new MarketDevEntities();
IEnumerable<WebLog> first = db.WebLogs;
var second = first.Where(c => c.DurationSeconds > 10);
var third = second.Where(c => c.WebLogID > 100);
var result = third.Where(c => c.EmailAddress.Length > 11);
Console.Write(result.First().UserName);
A command with the following syntax can be found in SQL Server profiler:
"SELECT * FROM [dbo].[WebLog]"
It approximately takes 90 seconds to run that block of code against a WebLog table which has 1 million records.
As a result, each table entry is put into memory as an object. Where() will be a memory-based filter against these items.
In the following example (second lin), we use IQueryable instead of IEnumerable.
A command with the following syntax can be found in SQL Server profiler:
"SELECT TOP 1 * FROM [dbo].[WebLog] WHERE [DurationSeconds] > 10 AND [WebLogID] > 100 AND LEN([EmailAddress]) > 11"
Running this block of code with IQueryable takes about four seconds.
IQueryable includes an Expression property that maintains a tree expression that begins to be built when we used the result in our example (this is known as postponed execution) and is eventually turned to a SQL query to run on the database engine.
Answered by Ramy Yousef
Solution #4
Yes, both will provide you deferred execution.
Which is preferable over the other is determined by your underlying datasource.
When you return an IEnumerable, the runtime will automatically utilize LINQ to Objects to query your collection.
Returning an IQueryable (which, by the way, implements IEnumerable) adds the ability to transform your query into something that may perform better on the underlying source (LINQ to SQL, LINQ to XML, etc.).
Answered by Justin Niessner
Solution #5
In general, I would advise you to do the following:
Consider an IQueryable to be exactly that – a data “query” (which you can refine if you want to). An IEnumerable is a collection of items that you may enumerate through (things that have already been received or generated).
Answered by sebastianmehler
Post is based on https://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet