Using an IEqualityComparer with a LINQ to Entities Except clause

Question

I have an entity that I'd like to compare with a subset and determine to select all except the subset.

So, my query looks like this:

Products.Except(ProductsToRemove(), new ProductComparer())

The ProductsToRemove() method returns a List<Product> after it performs a few tasks. So in it's simplest form it's the above.

The ProductComparer() class looks like this:

public class ProductComparer : IEqualityComparer<Product>
{
    public bool Equals(Product a, Product b)
    {
        if (ReferenceEquals(a, b)) return true;

        if (ReferenceEquals(a, null) || ReferenceEquals(b, null))
            return false;

        return a.Id == b.Id;
    }

    public int GetHashCode(Product product)
    {
        if (ReferenceEquals(product, null)) return 0;
        var hashProductId = product.Id.GetHashCode();
        return hashProductId;
    }
}

However, I continually receive the following exception:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[UnitedOne.Data.Sql.Product] Except[Product](System.Linq.IQueryable1[UnitedOne.Data.Sql.Product], System.Collections.Generic.IEnumerable1[UnitedOne.Data.Sql.Product], System.Collections.Generic.IEqualityComparer1[UnitedOne.Data.Sql.Product])' method, and this method cannot be translated into a store expression.

Answer 1

Linq to Entities isn't actually executing your query, it is interpreting your code, converting it to TSQL, then executing that on the server.

Under the covers, it is coded with the knowledge of how operators and common functions operate and how those relate to TSQL. The problem is that the developers of L2E have no idea how exactly you are implementing IEqualityComparer. Therefore they cannot figure out that when you say Class A == Class B you mean (for example) "Where Person.FirstName == FirstName AND Person.LastName == LastName".

So, when the L2E interpreter hits a method it doesn't recognize, it throws this exception.

There are two ways you can work around this. First, develop a Where() that satisfies your equality requirements but that doesn't rely on any custom method. In other words, test for equality of properties of the instance rather than an Equals method defined on the class.

Second, you can trigger the execution of the query and then do your comparisons in memory. For instance:

var notThisItem = new Item{Id = "HurrDurr"};
var items = Db.Items.ToArray(); // Sql query executed here
var except = items.Except(notThisItem); // performed in memory

Obviously this will bring much more data across the wire and be more memory intensive. The first option is usually the best.