Entity Framework allows to retrieve records from groups in any quantity and from any position, i.e. first record, records 1…N, second record, or records 5…10. These can be made with LINQ methods like First(), Take() and Skip().
When I write T-SQL code I often use ROW_NUMBER() function to generate serial numbers for groups of records, then filtering the required serial numbers of rows. I suspect that EF would generate a less efficient code with APPLY operator (look at this post Querying Entity Model. Part 7 – Left Join With First Row). Now it’s time to confirm this.
Retrieving first row
|
1 2 3 |
var query201 = ctx.Phones .GroupBy(p => p.CountryCode) .Select(g => g.FirstOrDefault()); |
Another way is to use Take() method.
|
1 2 3 |
var query202 = ctx.Phones .GroupBy(p => p.CountryCode) .SelectMany(g => g.Take(1)); |
Entity Framework uses SELECT DISTINCT to get group key values and APPLY operator to get a record for each group. EF uses OUTER APPLY in the first case, and CROSS APPLY in the second.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT [Limit1].[Id] AS [Id], [Limit1].[Number] AS [Number], [Limit1].[CountryCode] AS [CountryCode], [Limit1].[ClientId] AS [ClientId] FROM (SELECT DISTINCT [Extent1].[CountryCode] AS [CountryCode] FROM [dbo].[Phones] AS [Extent1] ) AS [Distinct1] OUTER APPLY (SELECT TOP (1) [Extent2].[Id] AS [Id], [Extent2].[Number] AS [Number], [Extent2].[CountryCode] AS [CountryCode], [Extent2].[ClientId] AS [ClientId] FROM [dbo].[Phones] AS [Extent2] WHERE ([Distinct1].[CountryCode] = [Extent2].[CountryCode]) OR (([Distinct1].[CountryCode] IS NULL) AND ([Extent2].[CountryCode] IS NULL)) ) AS [Limit1] |
Retrieving first N rows
|
1 2 3 |
var query203 = ctx.Phones .GroupBy(p => p.CountryCode) .SelectMany(g => g.Take(2)); |
The only difference in T-SQL code is the line CROSS APPLY (SELECT TOP (2).
Retrieving rows with offset
I try to get exactly the second rows from groups. Skip() method needs a sorted list, so I have to place OrderBy() before it.
|
1 2 3 |
var query204 = ctx.Phones .GroupBy(p => p.CountryCode) .SelectMany(g => g.OrderBy(x => x.Id).Skip(1).Take(1)); |
EF generates T-SQL code with OFFSET .. FETCH for SQL Server 2016.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT [Limit1].[Id] AS [Id], [Limit1].[Number] AS [Number], [Limit1].[CountryCode] AS [CountryCode], [Limit1].[ClientId] AS [ClientId] FROM (SELECT DISTINCT [Extent1].[CountryCode] AS [CountryCode] FROM [dbo].[Phones] AS [Extent1] ) AS [Distinct1] CROSS APPLY (SELECT [Project2].[Id] AS [Id], [Project2].[Number] AS [Number], [Project2].[CountryCode] AS [CountryCode], [Project2].[ClientId] AS [ClientId] FROM ( SELECT [Extent2].[Id] AS [Id], [Extent2].[Number] AS [Number], [Extent2].[CountryCode] AS [CountryCode], [Extent2].[ClientId] AS [ClientId] FROM [dbo].[Phones] AS [Extent2] WHERE ([Distinct1].[CountryCode] = [Extent2].[CountryCode]) OR (([Distinct1].[CountryCode] IS NULL) AND ([Extent2].[CountryCode] IS NULL)) ) AS [Project2] ORDER BY [Project2].[Id] ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY ) AS [Limit1] |