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

Aluan Haddad · Accepted Answer · 2019-10-14T15:58:44.480

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.

Gilad Green · Answer 2 · 2017-10-01T12:33:15.833

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:

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>.

score 1 · Answer 3 · answered Oct 01 '17 at 12:48

The previous answers explain how you need to specify a special comparer if you want to check the actual content and not the reference for equality.

One addition that probably should be mentioned:

If you do not especially need an List<T> or Array<T>, then you should refrain from calling .ToList<T>() and .ToArray<T>(). When you are using either of those, you are duplicating all items in the existing collection and copy them in a new one. Your memory is not amused by this when you handle large collections. Also the garbage collection necessary to dispose of the newly allocated memory can take a huge toll on overall performance. We had this cause a memory leak in our software before as our junior software developers were unaware of that.

If you can use IEnumerable<T>instead, the collection will be lazily streamed and thus no (or close to none) additional memory is allocated.

Sometimes you need to convert the collection into a List<T> or Array<T> due to access over an index or other reasons. Even then you should reduce the memory copies to a minimum.

LINQ Except get all rows

3 Answers3