Difference in execution between List and IQueryable

Question

I'm trying to write a generic database update method that can take advantage of IQueryable to whittle down the number of returned entities before processing. So for a part of the code, I tried this (b.ToType() returns a P):

IQueryable<B> bs = bcontext.Set<B>();
IQueryable<P> ps = pcontext.Set<P>();
List<P> inserts = ps.Except(bs.Select(b => b.ToType())).Take(500).ToList();

When I write it like this, I get System.ArgumentNullException: 'Value cannot be null.'

However, it works when I go ahead and enumerate the DBSets before doing the Except like this:

List<B> bs = bcontext.Set<B>().ToList();
List<P> ps = pcontext.Set<P>().ToList();
List<P> inserts = ps.Except(bs.Select(b => b.ToType())).Take(500).ToList();

Both ways compile fine, but I get the exception the first way and not the second way. Is there some limitation to what you can put in an IQueryable expression tree that doesn't exist with Lists?

Which LINQ provider are you using? EF6? EF Core? LINQ-to-SQL? Something else? Also, what happens if you write `IQueryable
ps2 = bs.Select(b => b.ToType());`, and what happens if you call `.ToList` on `ps2`? There are certainly limitations -- not so much in the expression tree (although there are such limits) -- but more what parts of the expression tree can be converted into SQL (or whatever the underlying data access language is). For example, EF6 against SQL Server will choke on a call to `.ToString`, because there is no simple way to translate that into SQL. But if that were the case ... — Zev Spitz, Jul 17 '18 at 23:10
... I would expect the error to be something more explicit: _LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression._ That's why I suspect that `bs.Select` is actually calling `Enumerable.Select` for some reason, and one of the results in `bs` is `null`. — Zev Spitz, Jul 17 '18 at 23:16
I'm using EF Core. `List
p1 = ps2.ToList()` works, and if I do `ps.Except(p1)` after that, it works. — Kristen Hammack, Jul 18 '18 at 21:11

score 2 · Answer 1 · answered Jul 17 '18 at 23:09

Generally IQueryable is used to avoid the execution of the query until it's narrowed down to the exact point where we will get the actual required data.

Compared to List, when we do .ToList() the query is executed and we have all the result in memory from where on you can query or filter out the results.

Depending upon the performance on the client side or the network correct option can be chosen. Doing .ToList will give you the result in memory from where on you can perform the operations.

For the reference ill redirect you to this answer: Differences between IQueryable, List, IEnumerator?

Mrinal Kamboj · Accepted Answer · 2018-07-18T13:02:41.123

2

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

edited Jul 18 '18 at 13:02

answered Jul 18 '18 at 12:53

Mrinal Kamboj

11,300
5
40
74

_Expression is more about what to do and Func is about how to do_ This isn't quite accurate; it's more accurate to say that `Func` points to a method which actually does something -- just like `Action action = Console.WriteLine;` -- while `Expression` is a data structure which describes the parts of code for some result -- you can't write `Expression expr = Console.WriteLine;` because `Console.WriteLine` is a real method; at best you could write `Expression expr = () => Console.WriteLine();` which the compiler would translate into a data structure containing "_call the ... – Zev Spitz Jul 19 '18 at 05:38
... `Console.WriteLine` method with no parameters_". As the answer you linked to states, lambda expression syntax can be used for both, and the compiler interprets the lambda expression differently based on whether it is being assigned to a `Func` or to an `Expression`. – Zev Spitz Jul 19 '18 at 05:38
Its like this, ultimately Expression can compile into Func, which is actual delegate containing method, example like yours works well for in memory processing, but when it comes to integration with remote providers like Mongo DB, Redis Cache, such implementation `Expression expr = () => Console.WriteLine();` has no significance, since they don't directly compile, they want information about What you want to achieve not How, they have their own inbuilt apis to achieve it, till the point they exactly know What part, which is the essence and use case for Expression Trees using IQueryable – Mrinal Kamboj Jul 19 '18 at 05:42
In fact we can go through detailed process to create Expression trees explaining various attributes and parameters in detail, which is not always possible using the Lambda syntax, which is more of the quick mechanism to compile for in memory processing. Would also work with MS providers which are tightly integrated, but not with 3rd party systems – Mrinal Kamboj Jul 19 '18 at 05:46
I was responding to what you said: _Expression is more about what to do and Func is about how to do_. Again, this isn't accurate. `Func` is an object which actually **does** (by pointing to a method); `Expression` is a data structure which describes **how to do** or **what to do**. – Zev Spitz Jul 19 '18 at 07:58
When discussing the differences between the `Func` (and related types) and `Expression>` (and related types), the fact that most providers don't make use of all possible operations in an expression tree is not a reflection of the difference between these two type families. Also, it is quite possible to use lambda expressions independent of `IQueryable` -- ASP.NET MVC allows passing a lambda expression which returns an anonymous type to pass name/property pairs; I've used it as an easier way of getting a `MethodInfo` than using straight-up reflection. – Zev Spitz Jul 19 '18 at 08:08
Just because you can use Expression tree in similar way as Func in **Only MS systems**, doesn't mean it tells you **How**, it is still **What**. **How** is always about the consuming / remote system, Expression tree is never intended for **How**, since its there's already a **Func**, why create a duplicate. If [this](https://stackoverflow.com/q/793571/1559611) can help with better understanding then great, otherwise you can continue with your view. – Mrinal Kamboj Jul 19 '18 at 08:29
Also Expression Tree can be used outside `IQueryable`, which is mostly to create `Func` at runtime but vice versa is never true, since `IQueryable` deals with remote systems, it always need Expression to explain **What** they need to achieve, **How** is not even a consideration here – Mrinal Kamboj Jul 19 '18 at 08:31
In fact as author in the [link](https://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct) I have posted mentioned something as simple as `Func myFunc = () => 10;` just returns a value 10 but it is not same as `Expression> myExpression = () => 10;`, since this creates a detailed data structure to explain **What** needs to be achieved, where **How** depends on consuming system, depending upon how to parse the details – Mrinal Kamboj Jul 19 '18 at 08:34

Difference in execution between List and IQueryable

2 Answers2