c# - 使用 LINQ，获取导航属性的前 x 行的方法是什么？

Question

我有一个对象的导航属性，比如 Items，比如 Order。如果我想获得订单列表，包括项目，我可以这样做:

var orders = dbContext.Order.Include(o => i.Items);

效果很好，但现在我只想为每个订单购买 3 件商品，我想知道实现此目的的最佳方法。

一种方法是执行以下操作:

var orders = 
    (from o in dbContext.Order
     join i in dbContext.Items on o.Id equals i.OrderId
     select new { o.Id, i })
    .GroupBy(o => o.Id)
    .SelectMany(i => i.Take(3))

这很好，虽然生成的 SQL 有点复杂，但我想知道是否有更直接(或更高效)的方法。

谢谢，埃里克

Answer 1

var orders = dbContext.Order
    .Select(o => new
    {
        Order = o,
        Items = o.Items.Take(3)
    })
    .AsEnumerable()
    .Select(a => a.Order)
    .ToList();

这将自动用前 3 项填充 Order.Items 集合

• 您没有禁用更改跟踪(上面的查询不是这种情况)
• Order 和Item 之间的关系不是多对多的(可能不是这种情况，因为订单和项目通常是一对多的关系)

编辑

生成的 SQL 查询是:

SELECT 
[Project2].[Id] AS [Id], 
[Project2].[C1] AS [C1], 
[Project2].[Id1] AS [Id1], 
[Project2].[OrderId] AS [OrderId], 
FROM (SELECT 
      [Extent1].[Id] AS [Id], 
      [Limit1].[Id] AS [Id1], 
      [Limit1].[OrderId] AS [OrderId], 
      CASE WHEN ([Limit1].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
      FROM  [dbo].[Orders] AS [Extent1]
      OUTER APPLY  (SELECT TOP (3) 
          [Extent2].[Id] AS [Id], 
          [Extent2].[OrderId] AS [OrderId], 
          FROM [dbo].[Items] AS [Extent2]
          WHERE [Extent1].[Id] = [Extent2].[OrderId] ) AS [Limit1]
) AS [Project2]
ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC