Returning IEnumerableT vs. IQueryableT
Yes, both will give you deferred execution.
The difference is that IQueryable<T>
is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable<T>
, that query will be executed in the database, if possible.
For the IEnumerable<T>
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<Customer> 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<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);
This is quite an important difference, and working on IQueryable<T>
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<T>
will cause all of your rows to be loaded in memory.
When should I use IEnumerable and when IQueryable?
IEnumerable<T>
represents something which produces a sequence of results. However, it doesn't expose any information about how the sequence is produced.
IQueryable<T>
exposes the information about how the sequence is to be produced, at the Expression
property, in the form of an expression tree. This information can then be easily mapped to a different set of instructions.
If you call Enumerable.Where
on an IEnumerable<T>
, you're passing in a compiled method compatible with Func<T, bool>
. In theory, we could parse the IL of the compiled method to figure out what the method does, and use that to map to another set of instructions; but that's very complex. Inevitably, the only way to work with this is to load all the objects into memory from the server/provider/datasource, and apply the compiled method on each object.
If you call Queryable.Where
on an IQueryable<T>
, you're passing in an object which by definition represents different code operations -- Expression<Func<T, bool>>
. For example:
IQueryable<Person> qry = /* ... */;
qry = qry.Where(x => x.LastName.StartsWith("A"));
the compiler converts x => x.LastName.StartsWith("A")
to an object representing its various parts:
Lambda expression, returning a `bool`
with an `x` parameter of type `Person`
Call the `StartsWith` method, passing in a constant string `"A"`, on
Result of the `LastName` property, of
the `x` element
More, calling Queryable.Where
itself also modifies the underlying expression tree:
Call to Queryable.Where, passing in
The object at `qry`, and
The previous lambda expression (see above)
When the query is enumerated (either with a foreach
, or a call to ToList
, or something similar), the information can easily be mapped from this object into another form, such as SQL:
SELECT *
FROM Persons
WHERE LastName LIKE N'A%'
or a web request:
http://example.com/Person?lastname[0]=a
The final expression tree after calling Queryable.Where
will look something like this object graph, if it were possible to construct expression trees using constructors and object and collection initializers:
var x = new ParameterExpression {
Type = typeof(Person),
Name = "x"
};
new MethodCallExpression {
Type = typeof(IQueryable<Person>),
Arguments = new ReadOnlyCollection<Expression> {
new ConstantExpression {
Type = typeof(EnumerableQuery<Person>)
},
new UnaryExpression {
NodeType = ExpressionType.Quote,
Type = typeof(Expression<Func<Person, bool>>),
Operand = new Expression<Func<Person, bool>> {
NodeType = ExpressionType.Lambda,
Type = typeof(Func<Person, bool>),
Parameters = new ReadOnlyCollection<ParameterExpression> {
x
},
Body = new MethodCallExpression {
Type = typeof(bool),
Object = new MemberExpression {
Type = typeof(string),
Expression = x,
Member = typeof(Person).GetProperty("LastName")
},
Arguments = new ReadOnlyCollection<Expression> {
new ConstantExpression {
Type = typeof(string),
Value = "A"
}
},
Method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) })
},
ReturnType = typeof(bool)
}
}
},
Method = typeof(Queryable).GetMethod("Where", new[] { typeof(IQueryable<Person>), typeof(Expression<Func<Person, bool>>) })
}
(NB. This was written using the ExpressionTreeToString library. Disclaimer: I am the author.)
IQueryable vs IEnumerable: is IQueryable always better and faster?
- If the
IQueryable
perform the query Expression in the server rather
than fetching all records likeIEnumerable
, whyIQueryable
not
replaced byIEnumerable
where it can be faster and more efficient?
IQueryable
and IEnumerable
represent two different things. Think of a IQueryable
as a "question", it does not have any results itself. A IEnumerable
is an "answer", it only has data connected to it but you can't tell what generated that data.
It is not that a IQueryable
is "faster" per say, it just allows you to put your filtering and projections in to the "question" you ask to the SQL server and let it return only the answers it needs to (In the form of a IEnumerable
by calling .ToList()
or similar).
If you only use a IEnumerable
the only question you can ask is "Give me everything you know" then on the answer it gives you you perform your filtering and projections. That is why IQueryable
is considered faster, because there is a lot less data that needs to be processed because you where able to ask a more specific question to the server.
The reason IQueryable
has not replaced IEnumerable
everywhere is because the thing you are asking a question has to be able to understand the question you are asking it. It takes a lot of work to be able to parse every possible thing you could ask it to filter or project on so most implementations limit themselves to only common things they know they need to be able to answer. For example in Entity Framework when you ask a question it does not understand how to handle you will get a error that says something similar to "Specified method is not supported" when you try to get a IEnumerable
(an answer) from the IQueryable
.
DBSet<T>
has two flavors ofWhere
(IQueryable
andIEnumerable
).
is there a way to call theIEnumerable
version because the
IQueryable
is called by default, without callingToList()
?
The class DBSet<T>
has no Where method on it at all. The two Where
functions come from two extension methods, Enumerable.Where
and Queryable.Where
. You can force it to use the Enumerable overload by casting the object to a IEnumerable<T>
before you call the extension method. However do remember, Queryable.Where
filters the question, Enumerable.Where
only filters the result.
It is wasteful to ask for results from the server to then just throw them away, so I would not recommend doing this.
Difference in execution between ListT and IQueryableT
Following is the implementation of the IQueryable<T>.Except
, check here :
public static IQueryable<TSource> Except<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2) {
if (source1 == null)
throw Error.ArgumentNull("source1");
if (source2 == null)
throw Error.ArgumentNull("source2");
return source1.Provider.CreateQuery<TSource>(
Expression.Call(
null,
GetMethodInfo(Queryable.Except, source1, source2),
new Expression[] { source1.Expression, GetSourceExpression(source2) }
));
}
Prime difference between the working of the IQueryable<T>
and List<T>
, Queryable type internally works with Expression<Func<T>>
, since its getting executed remotely, in your case using the provider, when List<T>
works with Func<T>
, since its an in memory processing. When it comes to remote processing something like EF translates into relevant Sql query for processing, when in your case the following translates to null
during remote processing: bs.Select(b => b.ToType())
.
Following is the implementation of IEnumerable<T>.Except
, check here:
public static IEnumerable<TSource> Except<TSource>(this IEnumerable<TSource> first,
IEnumerable<TSource> second)
{
if (first == null) throw Error.ArgumentNull("first");
if (second == null) throw Error.ArgumentNull("second");
return ExceptIterator<TSource>(first, second, null);
}
Except
itself is internally a set operation, even for List<T>
call to Except(null)
will lead to same exception.
As you have seen the definition of the IQueryable<T>.Except
, its important to understand the difference in processing of the Expression and Func
, Expression is more about what to do and Func is about how to do check this.
For a simple var intList = new List<int>{1,2,3}
, this is what Queryable expression looks like (as shown in the attached image).
Essence remains check what your provider is internally translating the Queryable Expression into, which is leading to null and thus exception while processing
Should I return IEnumerableT or IQueryableT from my DAL?
It depends on what behavior you want.
- Returning an IList<T> tells the caller that they've received all of the data they've requested
- Returning an IEnumerable<T> tells the caller that they'll need to iterate over the result and it might be lazily loaded.
- Returning an IQueryable<T> tells the caller that the result is backed by a Linq provider that can handle certain classes of queries, putting the burden on the caller to form a performant query.
While the latter gives the caller a lot of flexibility (assuming your repository fully supports it), it's the hardest to test and, arguably, the least deterministic.
Returning IEnumerable when the object is an IQueryable
when the statement is executed with a ToList() will the underlying SQL generated take into account the Id == id statement and so only return filter items
The short answer is 'Yes'.
There is only Enumerable.ToList<T>(this IEnumumerable<T> e)
and no counterpart for IQueryable<T>
Everything inside GetMyClasses
method will be operated as IQueryable<T>
, but the restrictions added after will be in-memory.
IEnumerable<MyClass> GetMyClasses(int id) {
return GetQuery().Where(p => p.Id==id); // this always will work as IQueryable and handled by provider (in case of EF - the DB query will be performed)
}
IEnumerable<MyClass> MoreRestrictions(int id) {
return GetMyClasses(id)
.ToList(); // result list will be filtered by Id
}
IEnumerable<MyClass> MoreRestrictions(int id) {
return GetMyClasses(id)
.Where(x=>x.IsActive) // this where will be performed in memory on results filtered by Id.
.ToList();
}
Methods ToList
and Where
is works like following
//There is only one implementation of ToList()
public List<T> ToList<T>(this IEnumerable<T> e)
{
var list = new List<T>();
var enumerator = e.GetEnumerator();
while (enumerator.MoveNext())
{
var item = e.Current;
list.Add(item);
}
return list;
}
//Implementation for IEnumerable
public IEnumerable<T> Where<T>(this IEnumerable<T> e, Predicate predicate)
{
var enumerator = e.GetEnumerator();
while (enumerator.MoveNext())
{
var item = e.Current;
if (predicate(item))
yield return item;
}
}
//Implementation for IQueryable
public IQueryable<T> Where<T>(this IQueryable<T> e, Expression<Predicate> predicate)
{
MethodBase method = ...; // create generic method of Queryable.Where()
return e.Provider
.CreateQuery<T>(Expression.Call(null, method, e.Expression, Expression.Quote(predicate)));
}
The GetEnumerator
method of IQueryable does query materialization.
Return IQueryableT as IEnumerableT will cause in database call?
Not if you don't do anything with it, no.
However, if you try to iterate over the results (or call Count()
etc) then it will try to make a database call... and I'd expect it to then fail, because you've disposed of the context at that point.
Related Topics
Passing Strings from C# to C++ Dll and Back - Minimal Example
How to Update the Gui from Another Thread
How to Parse a Json String That Would Cause Illegal C# Identifiers
Why Saving Changes to a Database Fails
How to Get the Application'S Path in a .Net Console Application
How to Convert a Column Number (E.G. 127) into an Excel Column (E.G. Aa)
In C#, Difference Between Public, Private, Protected, and Having No Access Modifier
How to Translate Between Windows and Iana Time Zones
How to Escape Braces (Curly Brackets) in a Format String in .Net
Using a Class Defined in a C++ Dll in C# Code
How to Use Reflection to Call a Generic Method
What Are Some Good .Net Profilers
C# Difference Between == and Equals()
How To: Execute Command Line in C#, Get Std Out Results
Returning Ienumerable≪T≫ Vs. Iqueryable≪T≫
Identify If a String Is a Number
How to Make Realistic N-Body Solar System Simulation in Matter of Size and Mass