IEquatable breaks loading of Entity Framework entities

Question

I want to compare records to see if there are differences between them.

Person table:

ID    Name          Address
--------------------------------
1     John Smith    123 A Street
2     John Smith    123 A Street
3     John Smith    234 B Street

Records 1 and 2 are "equal". Records 2 and 3 are "not equal".

I have implemented IEquatable on model Person as follows.

public static bool operator ==(Person p1, Person p2)
{
    if (System.Object.ReferenceEquals(p1, p2)) return true;

    return p1.Equals(p2);
}

public static bool operator !=(Person p1, Person p2)
{
    return !(p1== p2);
}

public bool Equals(Person other)
{
    if (System.Object.ReferenceEquals(this, other)) return true;

    if (Name != other.Name) return false;
    if (Address != other.Address) return false;

    return true;
}

public override bool Equals(object obj)
{
    Person person = obj as Person;
    if (person == null) return false;

    return Equals(person);
}

public override int GetHashCode()
{
    unchecked
    {
        int hash = (int)2166136261;
        hash = hash * 25165843 ^ (Name != null ? Name .GetHashCode() : 0);
        hash = hash * 25165843 ^ (Address != null ? Address.GetHashCode() : 0);

        return hash;
    }
}

The issue is that when the Persons ICollection from a navigational property is materialized. It is missing records "equal" to each other (ie a single John Smith 123 A Street record is returned). I am guessing this is because by default it considers distinct entities ones that have unique primary keys. By overriding equals it thinks both records are the same entity.

Screenshot showing Addresses instead of Persons: (Top is with IEquatable, bottom is without)

//Addresses Definition (generated code)
public virtual ICollection<Address> Addresses { get; set; }

How can I reconcile EF needing see equality at the object level versus me wanting to see a logical equality?

Answer 1

The key seems to be in the EF source code

In the remarks for EntityUtil.DetermineCollectionType(Type requestedType), there are these 'rules`:

    // The rules are:
    // If the collection is defined as a concrete type with a publicly accessible parameterless constructor, then create an instance of that type
    // Else, if HashSet{T} can be assigned to the type, then use HashSet{T}
    // Else, if List{T} can be assigned to the type, then use List{T}
    // Else, throw a nice exception.

So, from this it would seem that EF will new up a HashSet<Address> for your navigation property. This will use the default equality comparer and prevent any duplicates being added. As your Equals implementation identifies two of your results as equal, only one will be included.

Entities are usually uniquely identified - the overriding of the Equals ignoring the unique identifier is possibly not correct. The best solution would be to remove the override and to implement a separate IEqualityComparer. Most methods that use equality semantics will take this as an argument.

Answer 2

IEquatable and IEqualityComparer are concepts that are pretty much exclusive to LINQ to objects. EF has no practical way of translating any definition of "equality" defined in such a way into SQL, and so cannot perform such operations.

To get distinct items based on certain columns, in a way that can be translated into SQL, just group the items based on those values and then grab one item from each group:

var query = context.Table.GroupBy(row => new
    {
        row.Name,
        row.Address,
    })
    .Select(group => group.FirstOrDefault());

Answer 3

Don't use IEquatable etc, create your own AreEquivalent, or IsEquivalentTo method.