Entity Framework skips a group by

Question

I currently "swap" through a table ("Table 1"), which has the following fields: "Policy, name, amount, date", and in the policy "Table 1" there can be multi-sheet entries, for example:

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

How can I do this if I want to first create a group using a policy and then skip individual policies (basically, trying to make sure that the “page” contains all the entries for the policies included in this page)?

I use C #, an entity structure, and prefer lambda syntax if possible.

Answer 1

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

This generates the following SQL (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] 

Answer 2

The following gave me the desired results.

  return context.Tables1 .Where(i => context.Tables1 .GroupBy(t => t.Policy) .OrderBy(t => t.Key) .Skip(previousRecordCount).Take(page) .Select(t => t.Key) .Contains(i.Policy)) .ToList(); 

Answer 3

You can use the SkipWhile and TakeWhile , but you need to separate them as follows:

 var policyStart = context.Table1s.Orderby(i => i.Policy).ToList().SkipWhile(i => i.Policy == endingRecord.Policy); var firstPolicy = policyStart.FirstOrDefault().Policy; return policyStart.TakeWhile(i => i.Policy == firstPolicy).ToList();