Let’s imagine the situation when a person could have a few phone numbers, for example, home, work, mobile for home country, mobile for traveling, etc. I want to make a list of clients with just one phone number. This phone number can be chosen arbitrary or the first from an ordered list. This task can be done in Entity Framework Code First model by using a LINQ query.
I have Clients and Phones entities, and I’ll create a query in lambda syntax to select client names with a phone number with a first country code. I’ve found three ways to do this.
1. Join with first row using subquery
var query71 = ctx.Clients
.Select(c => new
.Where(p => p.ClientId == c.Id)
.OrderBy(p => p.CountryCode)
2. Join with first row using Navigation properties
var query72 = ctx.Clients
.Select(c => new
c.Phones.OrderBy(p => p.CountryCode).FirstOrDefault().Number
3. Join with first row using GroupJoin
var query73 = ctx.Clients
client => client.Id,
phone => phone.ClientId,
(client, phone) => new
phone.OrderBy(p => p.CountryCode).FirstOrDefault().Number
Entity Framework generates the same T-SQL query in all these cases.
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Limit1].[Number] AS [Number]
FROM [dbo].[Clients] AS [Extent1]
OUTER APPLY (SELECT TOP (1) [Project1].[Number] AS [Number]
FROM ( SELECT
[Extent2].[Number] AS [Number],
[Extent2].[CountryCode] AS [CountryCode]
FROM [dbo].[Phones] AS [Extent2]
WHERE [Extent2].[ClientId] = [Extent1].[Id]
) AS [Project1]
ORDER BY [Project1].[CountryCode] ASC ) AS [Limit1]
From the SQL Server perspective, using APPLY operator is not a good solution. APPLY executes a subquery for each client row. You can read more about APPLY operator in MSDN article Using APPLY.
The most efficient way for SQL Server will be a query with Window function such as ROW_NUMBER().
SELECT c.Name, p.Number,
ROW_NUMBER() OVER (
PARTITION BY c.Id
ORDER BY p.CountryCode
) AS row_num
FROM [dbo].[Clients] c
LEFT JOIN [dbo].[Phones] p ON c.Id = p.ClientId
SELECT Name, Number
WHERE row_num = 1;
To check the efficiency you need to insert a lot of records to your database, copy both scripts to SQL Server Management Studio and look at the Estimated or Actual Execution Plan. In a test with 4 client rows I have 74% and 26% relative to a batch. The percentages of the query cost could differ due to number of rows/data pages/clustered indexes to be processed, but the result is the query with APPLY is much heavier than that with a ROW_NUMBER() function.
I think you should avoid queries with APPLY to get many records. In these cases it would better to invoke a T-SQL queries or stored procedures from Entity Framework.