c# - 重写 ef core 2.1 中的相关子查询

Question

有没有办法重写此查询，使其不是相关子查询？

var query = (from o in dbcontext.Orders
                             let lastStatus = o.OrderStatus.Where(x => x.OrderId == o.Id).OrderByDescending(x => x.CreatedDate).FirstOrDefault()
                             where lastStatus.OrderId != 1
                             select new { o.Name, lastStatus.Id }
                             ).ToList();

这导致:

 SELECT [o].[Name], (
          SELECT TOP(1) [x0].[Id]
          FROM [OrderStatus] AS [x0]
          WHERE ([x0].[OrderId] = [o].[Id]) AND ([o].[Id] = [x0].[OrderId])
          ORDER BY [x0].[CreatedDate] DESC
      ) AS [Id]
      FROM [Orders] AS [o]
      WHERE (
          SELECT TOP(1) [x].[OrderId]
          FROM [OrderStatus] AS [x]
          WHERE ([x].[OrderId] = [o].[Id]) AND ([o].[Id] = [x].[OrderId])
          ORDER BY [x].[CreatedDate] DESC
      ) <> 1

我曾尝试在子查询上进行连接，但 EF 2.1 正在做一些奇怪的事情……不是我所期望的；

     var query = (from o in dbcontext.Orders
                     join lastStat in (from os in dbcontext.OrderStatus
                                       orderby os.CreatedDate descending
                                       select new { os }
                                       ) on o.Id equals lastStat.os.OrderId
                     where lastStat.os.StatusId != 1
                     select new { o.Name, lastStat.os.StatusId }).ToList();

Answer 1

在 EF6 中替换

let x = (...).FirstOrDefault()

与

from x in (...).Take(1).DefaultIfEmpty()

通常会生成更好的 SQL。

所以通常我会建议

var query = (from o in db.Set<Order>()
             from lastStatus in o.OrderStatus
                 .OrderByDescending(s => s.CreatedDate)
                 .Take(1)
             where lastStatus.Id != 1
             select new { o.Name, StatusId = lastStatus.Id }
            ).ToList();

(不需要 DefaultIfEmpty(左连接)，因为 where 条件无论如何都会将其转换为内部连接)。

不幸的是，目前(EF Core 2.1.4)存在翻译问题，因此上述导致客户评估。

当前的解决方法是用相关子查询替换导航属性访问器 o.OrderStatus:

var query = (from o in db.Set<Order>()
             from lastStatus in db.Set<OrderStatus>()
                 .Where(s => o.Id == s.OrderId)
                 .OrderByDescending(s => s.CreatedDate)
                 .Take(1)
             where lastStatus.Id != 1
             select new { o.Name, StatusId = lastStatus.Id }
            ).ToList();

它为 SqlServer 数据库生成以下 SQL(横向连接):

  SELECT [o].[Name], [t].[Id] AS [StatusId]
  FROM [Orders] AS [o]
  CROSS APPLY (
      SELECT TOP(1) [s].*
      FROM [OrderStatus] AS [s]
      WHERE [s].[OrderId] = [o].[Id]
      ORDER BY [s].[CreatedDate] DESC
  ) AS [t]
  WHERE [t].[Id] <> 1