c# - 删除不带 .Include 的选择 N+1

Question

考虑这些人为设计的实体对象:

public class Consumer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool NeedsProcessed { get; set; }
    public virtual IList<Purchase> Purchases { get; set; }  //virtual so EF can lazy-load
}

public class Purchase
{
    public int Id { get; set; }
    public decimal TotalCost { get; set; }
    public int ConsumerId { get; set; }
}

现在假设我要运行这段代码:

var consumers = Consumers.Where(consumer => consumer.NeedsProcessed);

//assume that ProcessConsumers accesses the Consumer.Purchases property
SomeExternalServiceICannotModify.ProcessConsumers(consumers);

默认情况下，这将受到 ProcessConsumers 方法内的 Select N+1 的影响。它会在枚举消费者时触发一个查询，然后它会 1 1 地获取每个购买集合。这个问题的标准解决方案是添加一个 include:

var consumers = Consumers.Include("Purchases").Where(consumer => consumer.NeedsProcessed);

//assume that ProcessConsumers accesses the Consumer.Purchases property
SomeExternalServiceICannotModify.ProcessConsumers(consumers);

这在很多情况下都可以正常工作，但在某些复杂的情况下，包含可能会彻底破坏性能几个数量级。是否有可能做这样的事情:

1. 捕获我的消费者，var consumers = _entityContext.Consumers.Where(...).ToList()
2. 获取我的购买，var purchases = _entityContext.Purchases.Where(...).ToList()
3. Hydrate the consumer.Purchases collections manually from the purchases I already loaded into memory。然后，当我将它传递给 ProcessConsumers 时，它不会触发更多的数据库查询。

我不确定如何做#3。如果您尝试访问任何将触发延迟加载的 consumer.Purchases 集合(并因此触发 Select N+1)。也许我需要将 Consumers 转换为正确的类型(而不是 EF 代理类型)然后加载集合？像这样:

foreach (var consumer in Consumers)
{
     //since the EF proxy overrides the Purchases property, this doesn't really work, I'm trying to figure out what would
     ((Consumer)consumer).Purchases = purchases.Where(x => x.ConsumerId = consumer.ConsumerId).ToList();
}

编辑:我已经稍微重写了示例，希望能更清楚地揭示问题。

Answer 1

如果我的理解正确，您希望在 1 个查询中加载消费者的过滤子集，每个子​​集都包含其购买的过滤子集。如果那不正确，请原谅我对您意图的理解。如果这是正确的，您可以执行以下操作:

var consumersAndPurchases = db.Consumers.Where(...)
    .Select(c => new {
        Consumer = c,
        RelevantPurchases = c.Purchases.Where(...)
    })
    .AsNoTracking()
    .ToList(); // loads in 1 query

// this should be OK because we did AsNoTracking()
consumersAndPurchases.ForEach(t => t.Consumer.Purchases = t.RelevantPurchases);

CannotModify.Process(consumersAndPurchases.Select(t => t.Consumer));

请注意，如果 Process 函数期望修改消费者对象，然后将这些更改提交回数据库，这将不起作用。