问题描述
我通过一个表格(表格1)对当前的分页进行分页,其中包含以下字段{Policy,Name,Amount,Date},并且在Table1中可以有多个记录,如下所示:
return context.Table1s.Orderby(i => i.Policy)
.Skip(endingRecord).Take页面)
.ToList();
如果我想先按策略进行分组,然后跳过并采取区分政策(基本上是为了确保一个页面包含页面中包含的策略的所有记录)?
我使用C#,实体框架并且更喜欢$ lambda语法。
(i => i.Policy).Skip(endingRecord).Take(g)页面)
.ToList();
生成这样的SQL(从LinqPad到Linq to SQL的示例):
pre $ SELECT $ [t4] ,[t4]。[Date]
FROM(
SELECT ROW_NUMBER()OVER(ORDER BY [t3]。[Policy])AS [ROW_NUMBER],[t3]。[test],[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 [tl]。[Policy] = [t2]。[Policy]))
)AS [t3]
)AS [t4]
WHERE [t4]。[ROW_NUMBER] BETWEEN @ p0 + 1 AND @ p0 + @ p1
ORDER BY [t4]。[ROW_NUMBER]
I a currently "paging" through a table ("Table1") that has the following fields { Policy, Name, Amount, Date} and there can be mulitple records in "Table1" for a policy, like the following:
return context.Table1s.Orderby(i => i.Policy)
.Skip(endingRecord).Take(page)
.ToList();
How would I do this if I wanted to first group by Policy and then skip and take on the distinct policies (basically trying to ensure that a "page" contains all the records for the policies included in the page)?
I'm using C#, entity framework and prefer the "lambda" syntax if possible.
return context.Table1s.GroupBy(i => i.Policy)
.Select(g => g.First())
.Orderby(i => i.Policy)
.Skip(endingRecord).Take(page)
.ToList();
That generates SQL like this (sample from 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]
这篇关于实体框架按组跳过的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!