c# - Entity Framework skip take by group by

Question

我目前正在通过一个表(“Table1”)进行“分页”，该表具有以下字段 { Policy, Name, Amount, Date} 并且在“Table1”中可以有多个策略记录，如下所示:

return context.Table1s.Orderby(i => i.Policy)
                      .Skip(endingRecord).Take(page)
                      .ToList();

如果我想先按策略分组，然后跳过并采用不同的策略(主要是试图确保“页面”包含页面中包含的策略的所有记录)，我该怎么做？

我使用的是 C# Entity Framework ，如果可能，我更喜欢“lambda”语法。

Answer 1

return context.Table1s.GroupBy(i => i.Policy)
                      .Select(g => g.First())
                      .Orderby(i => i.Policy)
                      .Skip(endingRecord).Take(page)
                      .ToList();

像这样生成 SQL(来自 LinqPad for Linq to SQL 的示例):

SELECT [t4].[test], [t4].[Name], [t4].[Policy], [t4].[Amount], [t4].[Date]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t3].[Policy]) AS [ROW_NUMBER], [t3].[test], [t3].[Name], [t3].[Policy], [t3].[Amount], [t3].[Date]
    FROM (
        SELECT [t0].[Policy]
        FROM Table1s AS [t0]
        GROUP BY [t0].[Policy]
        ) AS [t1]
    OUTER APPLY (
        SELECT TOP (1) 1 AS [test], [t2].[Name], [t2].[Policy], [t2].[Amount], [t2].[Date]
        FROM Table1s AS [t2]
        WHERE (([t1].[Policy] IS NULL) AND ([t2].[Policy] IS NULL)) OR (([t1].[Policy] IS NOT NULL) AND ([t2].[Policy] IS NOT NULL) AND ([t1].[Policy] = [t2].[Policy]))
        ) AS [t3]
    ) AS [t4]
WHERE [t4].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t4].[ROW_NUMBER]