Search code examples
linqienumerableiqueryableiequalitycomparer

What is the difference between an IQueryable's Contains() and an IEnumerable's Contains()?

I have an IQueryable custs, a Customer cust, a CustomerComparer custCp which implements IEqualityComparer.

When I call custs.Contains(cust, custCp) I get an exception: 

System.NotSupportedException: Unsupported overload used for query operator 'Contains'

But when I call custs.AsEnumerable().Contains(cust,custCp) it works. Can anyone explain why?

Solution

  • An operation on an IQueryable (in the case of Linq to Entities) is translated to SQL and executed in the database. Since you have an IEqualityComparer written in C#, the comparer can't be translated to SQL and the overload can't be supported.

    When you translate it to an IEnumerable using AsEnumerable(), all the data is transferred from the database to memory, where the overload can be easily supported. The downside of course being that you're transferring more data than necessary (potentially the whole table) to have it filtered in memory.