如何有效刷新多对多关系

发布于 2024-12-10 00:56:35 字数 493 浏览 1 评论 0原文

假设我有实体 A,它与 A 类型的另一个实体有多对多关系。因此,在实体 A 上,我有 A 的集合。假设我必须根据某些外部服务“更新”此关系 - 从时间开始有时候我收到通知,表明某些实体的关系已更改,以及当前相关实体的 ID 数组 - 有些关系可能是新的,有些关系可能是现有的,有些现有的不再存在...如何使用 EF 有效更新我的数据库? 一些想法:

  1. 急切地加载实体及其相关实体,对来自外部服务的 ID 集合进行 foreach,并根据需要删除/添加。但这不是很有效 - 需要加载可能数百个相关实体

  2. 清除当前关系并插入新关系。但如何呢?也许通过存储过程执行删除,然后通过“假”对象插入

    a.Related.Add(new A { Id = idFromArray })
    

但这可以在事务中完成吗? (调用存储过程,然后通过 SaveChanges 完成插入)

还是有第三种方法?

谢谢。

Lets say I have entity A, which have many to many relationship with another entities of type A. So on entity A, I have collection of A. And lets say I have to "update" this relationships according to some external service - from time to time I receive notification that relations for certain entity has changed, and array of IDs of current related entities - some relations can be new, some existing, some of existing no longer there... How can I effectively update my database with EF ?
Some ideas:

  1. eager load entity with its related entities, do foreach on collection of IDs from external service, and remove/add as needed. But this is not very effective - need to load possibly hundreds of related entities

  2. clear current relations and insert new. But how ? Maybe perform delete by stored procedure, and then insert by "fake" objects

    a.Related.Add(new A { Id = idFromArray })
    

but can this be done in transaction ? (call to stored procedure and then inserts done by SaveChanges)

or is there any 3rd way ?

Thanx.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

幸福丶如此 2024-12-17 00:56:35

好吧,“时不时”听起来不像是考虑性能改进的情况(除非您的意思是“从毫秒到毫秒”):)

无论如何,第一种方法是在没有存储过程的情况下进行此更新的正确想法。是的,您必须加载所有旧的相关实体,因为更新多对多关系只能通过 EF 更改检测。在不加载导航属性的情况下,没有可以利用的公开外键来更新关系。

详细的示例如下(昨天的新问题):

选择并选择在实体框架 4 中更新多对多

(只有“编辑”部分之前的最后一个代码片段与您的问题和编辑部分本身相关。)

对于第二个解决方案,您可以将整个操作包装到手动创建交易:

using (var scope = new TransactionScope())
{
    using (var context = new MyContext())
    {
        // ... Call Stored Procedure to delete relationships in link table

        // ... Insert fake objects for new relationships

        context.SaveChanges();
    }
    scope.Complete();
}

Well, "from time to time" does not sound like a situation to think much about performance improvement (unless you mean "from millisecond to millisecond") :)

Anyway, the first approach is the correct idea to do this update without a stored procedure. And yes, you must load all old related entities because updating a many-to-many relationship goes only though EFs change detection. There is no exposed foreign key you could leverage to update the relations without having loaded the navigation properties.

An example how this might look in detail is here (fresh question from yesterday):

Selecting & Updating Many-To-Many in Entity Framework 4

(Only the last code snippet before the "Edit" section is relevant to your question and the Edit section itself.)

For your second solution you can wrap the whole operation into a manually created transaction:

using (var scope = new TransactionScope())
{
    using (var context = new MyContext())
    {
        // ... Call Stored Procedure to delete relationships in link table

        // ... Insert fake objects for new relationships

        context.SaveChanges();
    }
    scope.Complete();
}
烟柳画桥 2024-12-17 00:56:35

好的,解决方案找到了。当然,纯 EF 解决方案是原始问题中提出的第一个解决方案。

但是,如果性能很重要,还有第三种方法,也是最好的方法,尽管它是 SQL Server 特定的(afaik) - 一种带有表值参数的过程。所有新的相关ID都进入,存储过程在事务中执行删除和插入。

在这里查找示例和性能比较(很棒的文章,我的解决方案基于它):

http://www.sommarskog.se/arrays-in-sql-2008.html

Ok, solution found. Of course, pure EF solution is the first one proposed in original question.

But, if performance matters, there IS a third way, the best one, although it is SQL server specific (afaik) - one procedure with table-valued parameter. All new related IDs goes in, and the stored procedure performs delete and inserts in transaction.

Look for the examples and performance comparison here (great article, i based my solution on it):

http://www.sommarskog.se/arrays-in-sql-2008.html

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文