I have three tables: Person, Books and PersonBook. Person and Books are the main tables and PersonBook is a "mapping table" between them.
The logic is very simple: Each person has multiple books. In the Add / Remove Book Screen, the Person can Add a new book or remove the existing book.
Note: I depicted only the limited column for easy understanding, but the actual table has large number of columns and much more constrains.
Person:
PersonId FirstName LastName
_____________________________________
1 Emma Watsan
2 John Peter
3 Albert Einsten
(....)
Book:
BookId BookName ISBN Author
___________________________________________________________
1 Beautiful Darkness
2 Bones Never Lies
3 The Lion
(...)
PersonBook:
Id PersonId BookId
___________________________
1 1 1
2 2 3
3 3 3
(...)
I have a list PersonBook, and I need to update it in the table using Entity State.
I'm currently doing the following:
Context.PersonBook.RemoveRange(Context.PersonBook.Where(m => m.PersonId == PersonId));
Context.PersonBook.AddRange(ListOfBooks);
Here I give a simple condition:
Context.PersonBook.Where(m => m.PersonId == PersonId)
But in reality, I have many checks.
My Requirement is to remove only the records which do not exist in the local collection ListOfBooks and add the records which do not exist in the DbSet.
Note: Each Person has 1K+ Books. In each edit request, comparing of all the records leads to performance degradation and what I am doing currently is better than that comparison, but it increases the primary key (auto Incremental Key
PersonBook.Id) value to very large numbers.