c# - Enumerable.Any()在大型Linq数据集上非常慢

我正在运行这样的代码：

var somethings = db.Somethings.Select(s => new SomethingViewModel
{
    Id = s.Id,
    Name = s.Name,
    IsActive = s.IsActive
    SubSomethings = s.SubSomethings.Select(ss => new SubSomethingViewModel
    {
        Id = ss.Id,
        Name = ss.Name,
        IsActive = ss.IsActive
    }).Where(wss => wss.IsActive)
}).Where(ws => ws.IsActive && (ws.SubSomethings.Any())) //remove elements if no SubSomethings
.ToList();

如您所见，这是一对多的关系。某物中有一个SubSomethings列表。如果我取出&&（ws.SubSomethings.Any（）），则会得到非常快速的返回列表。

但是，我只想将至少包含一个SubSomething的东西包括在列表中。我还尝试了以下方法，并获得了同样惊人的效率：

var somethings = db.Somethings.Select(s => new SomethingViewModel
{
    Id = s.Id,
    Name = s.Name,
    IsActive = s.IsActive
    SubSomethings = s.SubSomethings.Select(ss => new SubSomethingViewModel
    {
        Id = ss.Id,
        Name = ss.Name,
        IsActive = ss.IsActive
    }).Where(wss => wss.IsActive)
}).Where(ws => ws.IsActive)
.ToList(); //this finishes very quickly

var somethings2 = somethings.Where(s => s.SubSomethings.Any()).ToList(); //This is where the code bogged down

如何重新编写查询以使陷入困境的代码更快？需要注意的一件事：对于一两个记录，这很好用。当我达到> 8000条记录时，至少需要四分钟。

这是我在SubSomething表上为SomethingId的外键创建的索引，该索引对应于Something.Id。

CREATE NONCLUSTERED INDEX [IX_SubSomething_SomethingId] ON [dbo].[SubSomething]
(
    [SomethingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

这是SubSomething.SomethingId的外键创建：

ALTER TABLE [dbo].[SubSomething]  WITH CHECK ADD  CONSTRAINT [FK_SubSomething_Something_SomethingId] FOREIGN KEY([SomethingId])
REFERENCES [dbo].[Something] ([Id])
GO

ALTER TABLE [dbo].[SubSomething] CHECK CONSTRAINT [FK_SubSomething_Something_SomethingId]
GO

最佳答案

EF Core是您的问题。当前，当查询包含子集合投影时，执行N + 1个子查询是已知的。

解决此问题并将整个问题限制为2个SQL查询的唯一方法是尽可能过滤，然后使用急切的加载在内存中加载带有子实体的整个实体集，然后切换到LINQ to Objects并进行最终投影/过滤：

var somethings = db.Somethings
    .Include(s => s.SubSomethings)
    .Where(s => s.IsActive)
    .AsEnumerable()
    .Select(s => new SomethingViewModel
    {
        Id = s.Id,
        Name = s.Name,
        IsActive = s.IsActive,
        SubSomethings = s.SubSomethings.Select(ss => new SubSomethingViewModel
        {
            Id = ss.Id,
            Name = ss.Name,
            IsActive = ss.IsActive
        }).Where(wss => wss.IsActive).ToList()
    })
    .Where(s => s.SubSomethings.Any())
    .ToList();

关于c# - Enumerable.Any()在大型Linq数据集上非常慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43812336/