c# - 扁平化复杂的 LINQ to SQL

Question

我有一个有点复杂的 LINQ to SQL 查询，我正在尝试优化(不，不是过早，事情很慢)，有点像这样；

IQueryable<SearchListItem> query = DbContext.EquipmentLives
    .Where(...)
    .Select(e => new SearchListItem {
        EquipmentStatusId = e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null).Id,
        StatusStartDate = e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null).DateFrom,
        ...
    });

where 子句并不重要，它们不过滤 EquipmentStatuses，如果有人认为需要它们，我们很乐意包含。

这是在相当大的一组表上，并返回一个相当详细的对象，还有更多对 EquipmentStatuses 的引用，但我相信你明白了。问题是很明显有两个子查询，我敢肯定这(除其他外)并不理想，尤其是因为它们每次都是完全相同的子查询。

有没有可能把它弄平一点？也许对数据库执行一些较小的查询并在 foreach 循环中创建 SearchListItem 会更容易？

Answer 1

这是我根据您的评论和我所做的一些假设得出的结论

• 它可能看起来很可怕，但请尝试一下，在 GroupBy() 之前使用和不使用 ToList()
• 如果您有 LinqPad，检查生成的 SQL 和查询数量，或者直接插入 SQL Server Profiler
• 使用 LinqPad，您甚至可以放置一个秒表来精确测量事物

享受 ;)

var query = DbContext.EquipmentLives
    .AsNoTracking() // Notice this!!!
    .Where(...)

    // WARNING: SelectMany is an INNER JOIN
    // You won't get EquipmentLive records that don't have EquipmentStatuses
    // But your original code would break if such a case existed
    .SelectMany(e => e.EquipmentStatuses, (live, status) => new
    {
        EquipmentLiveId = live.Id, // We'll need this one for grouping
        EquipmentStatusId = status.Id,
        EquipmentStatusDateTo = status.DateTo,
        StatusStartDate = status.DateFrom
        //...
    })

    // WARNING: Again, you won't get EquipmentLive records for which none of their EquipmentStatuses have a DateTo == null
    // But your original code would break if such a case existed
    .Where(x => x.EquipmentStatusDateTo == null)

    // Now You can do a ToList() before the following GroupBy(). It depends on a lot of factors...
    // If you only expect one or two EquipmentStatus.DateTo == null per EquipmentLive, doing ToList() before GroupBy may give you a performance boost
    // Why? GroupBy sometimes confuses the EF SQL generator and the SQL Optimizer
    .GroupBy(x => x.EquipmentLiveId, x => new SearchListItem
    {
        EquipmentLiveId = x.EquipmentLiveId, // You may or may not need this?
        EquipmentStatusId = x.EquipmentStatusId,
        StatusStartDate = x.StatusStartDate,
        //...
    })

    // Now you have one group of SearchListItem per EquipmentLive
    // Each group has a list of EquipmenStatuses with DateTo == null
    // Just select the first one (you could do g.OrderBy... as well)
    .Select(g => g.FirstOrDefault())

    // Materialize
    .ToList();