How to get an overloaded == operator to work with LINQ and EF Core?

Question

so basically, I have a project which uses EF Core. In order to shorten my lambdas when comparing if two objects (class Protocol) are equal, I've overridden my Equals method and overloaded the == and != operators. However, LINQ doesn't seem to care about it, and still uses reference for determining equality. Thanks

As I've said before, I've overridden the Equals method and overloaded the == and != operators. With no luck. I've also tried implementing the IEquatable interface. Also no luck.

I am using: EF Core 2.2.4

//the protocol class

[Key]
public int ProtocolId {get;set;}
public string Repnr {get;set;}
public string Service {get;set;}

public override bool Equals(object obj)
{
    if (obj is Protocol other)
    {
        return this.Equals(other);
    }
    return false;
}

public override int GetHashCode()
{
    return $"{Repnr}-{Service}".GetHashCode();
}

public bool Equals(Protocol other)
{
    return this?.Repnr == other?.Repnr && this?.Service == other?.Service;
}

public static bool operator ==(Protocol lhs, Protocol rhs)
{
    return lhs.Equals(rhs);
}

public static bool operator !=(Protocol lhs, Protocol rhs)
{
    return !lhs.Equals(rhs);
}

//the problem

using (var db = new DbContext())
{

     var item1 = new Protocol() { Repnr = "1666", Service = "180" };
     db.Protocols.Add(item1 );
     db.SaveChanges();
     var item2 = new Protocol() { Repnr = "1666", Service = "180" };
     var result1 = db.Protocols.FirstOrDefault(a => a == item2);
     var result2 = db.Protocols.FirstOrDefault(a => a.Equals(item2));
     //both result1 and result2 are null

}

I would expect both result1 and result2 to be item1. However, they're both null. I know I could just do a.Repnr == b.Repnr && a.Service == b.Service, but that just isn't as clean. Thanks

Answer 1

To understand why the incorrect equality comparer is used, you have to be aware about the difference between IEnumerable<...> and IQueryable<...>.

IEnumerable

An object that implements IEnumerable<...>, is an object that represents a sequence of similar objects. It holds everything to fetch the first item of the sequence, and once you've got an item of the sequence you can get the next item, as long as there is a next item.

You start enumerating either explicitly by calling GetEnumerator() and repeatedly call MoveNext(). More common is to start enumerating implicitly by using foreach, or LINQ terminating statements like ToList(), ToDictionary(), FirstOrDefault(), Count() or Any(). This group of LINQ methods internally uses either foreach, or GetEnumerator() and MoveNext() / Current.

IQueryable

An object that implements IQueryable<...> also represents an enumerable sequence. The difference however, is that this sequence usually is not held by your process, but by a different process, like a database management system.

The IQueryable does not (necessarily) hold everything to enumerate. Instead it holds an Expression and a Provider. The Expression is a generic description about what must be queried. The Provider knows which process will execute the query (usually a database management system) and how to communicate with this process (usually something SQL-like).

An IQueryable<..> also implements IEnumerable<..>, so you can start enumerating the sequence as if it was a standard IEnumerable. Once you start enumerating an IQueryable<...> by calling (internally) GetEnumerator(), the Expression is sent to the Provider, who translates the Expression into SQL and executes the query. The result is presented as an enumerator, which can be enumerated using MoveNext() / Current.

This means, that if you want to enumerate an IQueryable<...>, the Expression must be translated into a language that the Provider supports. As the compiler does not really know who will execute the query, the compiler can't complain if your Expression holds methods or classes that your Provider doesn't know how to translate to SQL. In such cases you'll get a run-time error.

It is easy to see, that SQL does not know your own defined Equals method. In fact, there are even several standard LINQ functions that are not supported. See Supported and Unsupported LINQ Methods (LINQ to Entities).

So what should I do if I want to use an unsupported function?

One of the things that you could do is move the data to your local process, and then call the unsupported function.

This can be done using ToList, but if you will only use one or a few of the fetched items, this would be a waste of processing power.

One of the slower parts of a database query is the transport of the selected data to your local process. Hence it is wise to limit the data to the data that you actually plan to use.

A smarter solution would be to use AsEnumerable. This will fetch the selected data "per page". It will fetch the first page, and once you've enumerated through the fetched page (using MoveNext), it will fetch the next page.

So if you only use a few of the fetched items, you will have fetched some items that are not used, but at least you won't have fetched all of them.

Example

Suppose you have a local function that takes a Student as input and returns a Boolean

bool HasSpecialAbility(Student student);

Requirement: give me three Students that live in New York City that have the special Ability.

Alas, HasSpecialAbility is a local function, it can't be translated into Sql. You'll have to get the Students to your local process before calling it.

var result = dbContext.Students
    // limit the transported data as much as you can:
    .Where(student => student.CityCode == "NYC")

    // transport to local process per page:
    .AsEnumerable()

    // now you can call HasSpecialAbility:
    .Where(student => HasSpecialAbility(student))
    .Take(3)
    .ToList();

Ok, you might have fetched a page of 100 Students while you only needed 3, but at least you haven't fetched all 25000 students.