Querying Entity Model. Part 20 – Retrieving Some Records From Groups

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

Another way is to use Take() method.

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.

Retrieving first N rows

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.

EF generates T-SQL code with OFFSET .. FETCH for SQL Server 2016.

Leave a Reply

Your email address will not be published. Required fields are marked *