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

You're trying to convert the Except call with your custom IEqualityComparer into Entity SQL.

Obviously, your class cannot be converted into SQL.

You need to write Products.AsEnumerable().Except(ProductsToRemove(), new ProductComparer()) to force it to execute on the client. Note that this will download all of the products from the server.

---

By the way, your ProductComparer class should be a singleton, like this:

public class ProductComparer : IEqualityComparer<Product> {
    private ProductComparer() { }
    public static ProductComparer Instance = new ProductComparer();

    ...
}

Answer 2

The IEqualityComparer<T> can only be executed locally, it can't be translated to a SQL command, hence the error

Answer 3

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.