Difference in execution between List<T> and IQueryable<T>

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?

Answer 1

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