Get unique items from two lists-CodePudding

I've a Student table in DB. I'm trying to add new data to it.

I take a in-memory list of students, check if these records already exists in db. If there is at least one record that doesn't exist in db, I will add it.

I tried several ways and some of them worked. But, it was not optimal. What I tried :

Taking every record from db and comparing one-by-one.
Taking every record from in-memory list and querying db to check if there is a relevant record.

They both are bad. Now I tried a solution that looks more optimal but I can't make it work.

List<Student>? inMemoryStudents = GetStudents();
List<Student>? existingStudentsFromDb = myDbContext.Students.Where(u => inMemoryStudents.Contains(u)).ToList();
IEnumerable<Student> newStudents = inMemoryStudents.Except(existingStudentsFromDb);
            
myldcDbContext.Students.AddRange(newStudents);

Except doesn't work. Example:

If I have 30 students in inMemoryStudents and all 30 of them exist in db, I still get all 30 records in newStudents variable.

CodePudding user response：

The Except LINQ method uses objects' equality methods to determine whether they're the same. By default, most C# objects will only be considered "equal" if they are the exact same in-memory object.

Try using IDs instead of comparing entire Student objects.

List<Student> inMemoryStudents = GetStudents()!;
List<int> inMemoryStudentIds = inMemoryStudents.Select(i => i.Id).ToList();
HashSet<int> existingStudentIdsFromDb = myDbContext.Students
    .Where(u => inMemoryStudentsIds.Contains(u.Id))
    .Select(u => u.Id)
    .ToHashSet();
IEnumerable<Student> newStudents = inMemoryStudents
    .Where(s => !existingStudentIdsFromDb.Contains(s.Id));
            
myldcDbContext.Students.AddRange(newStudents);

Besides addressing the equality issue, this has the advantage of only sending and receiving integers to and from the database, rather than all the student data you don't really need.

CodePudding user response：

Except takes the set difference of two lists. To determine what is a difference, an equality operator will be applied. Since the list returned from the database will be different objects as in memory (although with same properties and values), it will never return true.

A possible solution would be to override the == operator and make it check for each individual property and/or use solely the StudentID/ID field (assuming there is such a field)

CodePudding user response：

You want to save students that their Ids are not in the database. So just check that MemoryStudents list does not the IDs in StudentStudentsFromDb list.

List<Student> newStudents = inMemoryStudents.Where( i => !existingStudentsFromDb.Select(item=>item.Id).Contains(i.Id)).ToList();

About existingStudentsFromDb list, you can use other method that return just Ids. So you can change above code like this :

List<Student> newStudents = inMemoryStudents.Where( i => !existingStudentsFromDb.Contains(i.Id)).ToList();