LINQ Except get all rows

Question

I use Except method in list that contains DB Tables Objects , I have list of the existing rows on DB,and list of values from app, I have to check the delta between this 2 lists and to delete/insert to DB, I use except, but its return all the values. code:

public void UpdatePoints(IEnumerable<TBL_Points> selectedPoints, int id)
{

    List<TBL_Points> plistPointsFromDB = Context.TBL_Points.Where(x => x.Code == id).ToList();         
    List<TBL_Points> pRemoveItems = plistPointsFromDB .Except<TBL_Points>(selectedPoints.ToList()).ToList();
    List<TBL_Points> pAddItems = selectedPoints.ToList().Except<TBL_Points>(plistPointsFromDB).ToList();
}

the pRemoveItems get all the values from plistPointsFromDB, and the pAddItems get all values from selectedPoints

Answer 1

The default equality semantics for CLR reference types are to compare by object identity. Unless two references refer to the same object instance, they are considered distinct.

Especially in scenarios such as this, where set operations are desirable, we may need to customize this behavior, providing our objects with value semantics, not reference semantics.

There are multiple ways to achieve this. We can define it at the algorithm level, passing a custom comparison into the Except method. Or we can define it at the type level, defining equality for all instances of the set element type in question. Which approach is best will depend partially on how the objects are used and if we can modify the source code for the class defining the element type of the sets.

In his answer, Gilad Green provides very helpful references to Microsoft documentation on the nature and use cases for these approaches.

In the following example, we use a custom comparer to define equality.

sealed class PointComparer: EqualityComparer<TBL_Points>
{
    public override bool Equals(TBL_Points x, TBL_Points y) => x.Code == y.Code;

    public override int GetHashCode(TBL_Points point) => point.Code.GetHashCode();
}

We now need to instantiate this comparer and pass it into Except like so

var pointsToAdd = selectedPoints
    .AsEnumerable()
    .Except(plistPointsFromDB, new PointComparer())
    .ToList();

Note that I cleaned up the naming (except for that of the type itself which is still awful) and removed the unnecessary use of explicit types.

The other approach is to define equality for the type TBL_Points itself (again that needs renaming)

class TBL_Points: IEquatable<TBL_Points>
{
    public bool Equals(TBL_Points other) => Code == other?.Code;

    public sealed override bool Equals(object obj) => obj is TBL_Points o && Equals(o);

    public sealed override int GetHashCode() => Code.GetHashCode();

    public static bool operator ==(TBL_Points x, TBL_Points y) => x?.Equals(y) == true;

    public static bool operator !=(TBL_Points x, TBL_Points y) => !(x == y);
}

The above defines equality semantics for all uses of the type. This is convenient, since we no longer need to create and pass instances of a comparison object into algorithms. This also ensures consistency across all uses.

Answer 2

Except, unless specified other, uses the default comparer which calls Equals and GetHashCode. These, unless overridden compare reference equality which means that if you have two different instances, even if with same values, they are not equal. You need to :

• Override the Equals and GetHashCode of TBL_Points
• Or specify an IEqualityComparer<TBL_Points>

Read more on:

• What is the best algorithm for an overridden System.Object.GetHashCode?
• Guidelines for Overriding Equals() and Operator ==

---

Also no need for so many ToList() calls.

var fromDB = Context.TBL_Points.Where(x => x.Code == id).ToList(); 
var remove = fromDB.Except(selectedPoints);
var add = selectedPoints.Except(fromDB);

The one that I left is to execute the query to DB. For the other cases it is fine to work with an IEnumerable<T>.