c# - EF Core 3.0 .Include() 链的时间比 2.2 长约 5-10 倍

Question

我在处理包含大量链接的大型 EF Core 查询时遇到问题 .Include() .
我有一个看起来像这样的 linq 查询:

_context.Equipment.Include(x => x.Group)
                .Include(x => x.Status)
                .Include(x => x.Area)
                .Include(x => x.EquipmentType)
                .Include(x => x.Parts).ThenInclude(x => x.ChildrenParts)
                .Include(x => x.Parts).ThenInclude(x => x.ParentParts)
                .Include(x => x.Parts).ThenInclude(x => x.Vendor)
                .Include(x => x.Notes)
                .Include(x => x.Department)
                .Include(x => x.PMaintenance)
                .Include(x => x.SystemInfo).ThenInclude(x => x.SystemUsers)
                .Include(x => x.SystemInfo).ThenInclude(x => x.Frameworks)
                .Include(x => x.SystemInfo).ThenInclude(x => x.VideoCards)
                .Include(x => x.SystemInfo).ThenInclude(x => x.StorageDrives)
                .Include(x => x.SystemInfo).ThenInclude(x => x.Software)
                .Include(x => x.SystemInfo).ThenInclude(x => x.NetworkAdapters)
                .Include(x => x.SystemInfo).ThenInclude(x => x.Printers)
                .Include(x => x.MaintenanceHours)
                .Include(x => x.Attachments)
                .Include(x => x.Request)
                .FirstOrDefault(x => x.EquipmentId == id);

在 EF Core 2.2 中，这需要不到几秒钟的时间来评估。现在在 EF Core 3.0 上，大约需要 15 秒才能完成。 EF Core 3 怎么办这么慢？我读了 here该 ef 现在为每个 linq 查询创建一个 sql 语句，但我看不到该语句在此实例中将如何更改。我可以对这个查询做些什么来减少执行时间吗？

编辑:这是在 SQL Server v11.0.3 上

Answer 1

像这样试试。您可能需要更改某些“Select”和“SelectMany”选项以及 Id 字段名称，因为您没有发布上下文。`

var query = _context.Equipment.Include(x => x.Group)
            .Include(x => x.Status)
            .Include(x => x.Area)
            .Include(x => x.EquipmentType)
            .Include(x => x.Notes)
            .Include(x => x.Department)
            .Include(x => x.PMaintenance)
            .Include(x => x.MaintenanceHours)
            .Include(x => x.Attachments)
            .Include(x => x.Request).FirstOrDefault(x => x.EquipmentId == id);

            query.Include(x => x.Parts).ThenInclude(x => x.ChildrenParts).SelectMany(x => x.Parts).Where(a => query.Select(q => q.PartsId).Contains(a.Id)).Load();
            query.SelectMany(x => x.Parts).SelectMany(x => x.ChildrenParts).Load();
            query.SelectMany(x => x.Parts).SelectMany(x => x.ParentParts).Load();
            query.SelectMany(x => x.Parts).Select(x => x.Vendor).Load();
            query.Include(x => x.SystemInfo).ThenInclude(x => x.SystemUsers).SelectMany(x => x.SystemInfo).Where(a => query.Select(q => q.SystemInfoId).Contains(a.Id)).Load();
            query.SelectMany(x => x.SystemInfo).SelectMany(x => x.Frameworks).Load();
            query.SelectMany(x => x.SystemInfo).SelectMany(x => x.VideoCards).Load();
            query.SelectMany(x => x.SystemInfo).SelectMany(x => x.StorageDrives).Load();
            query.SelectMany(x => x.SystemInfo).SelectMany(x => x.Software).Load();
            query.SelectMany(x => x.SystemInfo).SelectMany(x => x.NetworkAdapters).Load();
            query.SelectMany(x => x.SystemInfo).SelectMany(x => x.Printers).Load();

            query.ToList();

希望这可以帮助。