实体框架中的大规模添加和删除多对多关系 [英] Large Scale Additions and Deletions Many-to-Many Relationship in Entity Framework

问题描述

我目前正在从事一个最近已获得许多数据库记录的项目.我现在正在尝试对此进行优化.这是我的模特:

I am currently working on a project that has recently acquired many database records. I now am trying to optimize for this. Here are my models:

public class QueueEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<PhraseEntity> Phrases { get; set; }
}

public class PhraseEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual SourceEntity Source { get; set; }
    public virtual List<QueueEntity> Queues { get; set; }
}

public class SourceEntity 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<PhraseEntity> Phrases { get; set; }
}

我现在正在测试约100,000个短语记录，并说这些短语的每20,000增量都属于不同的来源.

I am now testing with about 100,000 phrase records, and say each 20,000 increment of these phrases belongs to a different source.

用户可以从队列中添加和删除源"；本质上，我只是添加或删除从源到队列的所有短语.这种结构不是我的选择，现在更改为时已晚.

Users have the ability to add and delete 'Sources' from a Queue; essentially, I just add or delete all the phrases from the source to the queue. This structure was not my choice, and it is too late for it to be changed.

这些保存和删除操作现在导致我的测试系统崩溃，因为在没有将它们拉入内存的情况下，我似乎无法访问队列中的短语.即使尝试批量删除(例如，context.Queues.Single(m => m.Id == queueId).Phrases.Take(1000);或将IQueryable的1000个短语存储为变量短语"并执行context.Queues.Single(m => m.Id == queueId).Phrases.RemoveAll(m => phrases.Contains(m));)，似乎我仍在尝试将队列中的所有短语记录拉入内存.

These saves and deletions are now causing my test system to crash, as I can't seem to access the Phrases on a Queue without pulling them into memory. Even when attempting a batch delete (e.g. context.Queues.Single(m => m.Id == queueId).Phrases.Take(1000); or getting an IQueryable of 1000 phrases stored as variable 'phrases' and performing context.Queues.Single(m => m.Id == queueId).Phrases.RemoveAll(m => phrases.Contains(m));) it seems that I am attempting to pull all phrase records on that queue into memory.

在仍然使用LINQ表达式的情况下，有没有更简单的方法来删除这些记录之间的关系?

Is there a simpler way to remove the relationship between these records while still using a LINQ expression?

推荐答案

如果其他任何人也遇到同样的问题，我们决定在这种情况下不使用ORM.我们只编写了两个SQL存储过程(一个用于添加，一个用于删除)，这简化了问题并极大地提高了效率.在对100,000条以上的记录进行测试时，如果不抛出内存不足异常，则需要花费大量的时间才能完成操作.使用存储过程，我们的操作在几秒钟或更短的时间内完成.从可测试性的角度来看，这种方法的唯一缺点.

In case anyone else stumbles across this question with the same issue, we decided against using the ORM for this situation. We just wrote two SQL stored procedures (one for additions and one for deletions), and this simplified the problem and increased efficiency immensely. When testing with 100,000+ records, it would take a ridiculous amount of time to complete the actions if it didn't throw an out of memory exception. With the stored procedures, our operations were completed in a couple seconds or less. The only downfalls to this approach come from a testability standpoint.

这篇关于实体框架中的大规模添加和删除多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！