Get unique items from two lists

Question

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.

Answer 1

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.