c# - 无需手动比较即可添加或删除多条记录

Question

我有三个表:Person、Books 和 PersonBook。 Person和Books是主表，PersonBook是它们之间的“映射表”。

逻辑很简单:每个人都有多本书。在添加/删除书籍屏幕中，此人可以添加新书或删除现有书籍。

Note: I depicted only the limited column for easy understanding, but the actual table has large number of columns and much more constrains.

人:

PersonId    FirstName    LastName
_____________________________________
1           Emma         Watsan
2           John         Peter
3           Albert       Einsten
(....)

书:

BookId      BookName                ISBN           Author           
___________________________________________________________
1           Beautiful Darkness
2           Bones Never Lies
3           The Lion
(...)

个人簿:

Id    PersonId     BookId
___________________________
1     1            1
2     2            3
3     3            3
(...)

我有一个列表 PersonBook，我需要使用实体状态在表中更新它。

我目前正在做以下事情:

Context.PersonBook.RemoveRange(Context.PersonBook.Where(m => m.PersonId == PersonId));
Context.PersonBook.AddRange(ListOfBooks);

这里我给出一个简单的条件:

Context.PersonBook.Where(m => m.PersonId == PersonId)

但实际上，我有很多支票。

我的要求是仅删除本地集合 ListOfBooks 中不存在的记录，并添加 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.

Answer 1

如果您准备并使用快速查找数据结构，比较会更快。因为在这里你正在处理人的书，它可能是一个 Dictionary<int, PersonBook> BookId 的新旧记录作为 key :

var oldBooks = Context.PersonBook.Where(e => e.PersonId == PersonId).ToDictionary(e => e.BookId);
var newBooks = ListOfBooks.ToDictionary(e => e.BookId);
Context.PersonBook.RemoveRange(oldBooks.Values.Where(e => !newBooks.ContainsKey(e.BookId)));
Context.PersonBook.AddRange(newBooks.Values.Where(e => !oldBooks.ContainsKey(e.BookId)));