ASP.NET Tips #67 - Use LINQ's 'let' keyword to tune emitted SQL

Projecting a row into an object with a nested object has a big impact on the generated SQL. For example, here is an original query:

from s in Scholars
where s.ID == 2764
select new
{
   s.School.Address1,
   s.School.Address2,
   s.School.City,
   s.School.State,
   s.School.ZIP,
   s.School.PhoneNo,
   s.School.Email,
   Principal_FirstName = s.School.Leader.FirstName,
   Principal_LastName = s.School.Leader.LastName,
   Principal_Email = s.School.Leader.Email
}

This generates the following SQL:

SELECT
1 AS [C1],
[Extent2].[Address1] AS [Address1],
[Extent2].[Address2] AS [Address2],
[Extent2].[City] AS [City],
[Extent2].[State] AS [State],
[Extent2].[ZIP] AS [ZIP],
[Extent2].[PhoneNo] AS [PhoneNo],
[Extent2].[Email] AS [Email],
[Join2].[FirstName] AS [FirstName],
[Join4].[LastName] AS [LastName],
[Join6].[Email] AS [Email1]
   FROM [dbo].[Scholar] AS [Extent1]
   LEFT OUTER JOIN [dbo].[School] AS [Extent2] ON [Extent1].
[SchoolID] = [Extent2].[ID]
LEFT OUTER JOIN (SELECT [Extent3].[ID] AS [ID1],
[Extent4].[FirstName] AS [FirstName]
   FROM [dbo].[Staff] AS [Extent3]
   INNER JOIN [dbo].[Person] AS [Extent4] ON [Extent3].
[ID] = [Extent4].[ID] ) AS [Join2] ON [Extent2].
[LeaderStaffID] = [Join2].[ID1]
LEFT OUTER JOIN (SELECT [Extent5].[ID] AS [ID2],
[Extent6].[LastName] AS [LastName]
   FROM [dbo].[Staff] AS [Extent5]
   INNER JOIN [dbo].[Person] AS [Extent6] ON [Extent5].
[ID] = [Extent6].[ID] ) AS [Join4] ON [Extent2].
[LeaderStaffID] = [Join4].[ID2]
LEFT OUTER JOIN (SELECT [Extent7].[ID] AS [ID3],
[Extent8].[Email] AS [Email]
   FROM [dbo].[Staff] AS [Extent7]
   INNER JOIN [dbo].[Person] AS [Extent8] ON [Extent7].
[ID] = [Extent8].[ID] ) AS [Join6] ON [Extent2].
[LeaderStaffID] = [Join6].[ID3]
WHERE 2764 = [Extent1].[ID]

Using the 'let' keyword allows us to define the navigation as an alias:

from s in Scholars
where s.ID == 2764
let leader = s.School.Leader
select new
{
   s.School.Address1,
   s.School.Address2,
   s.School.City,
   s.School.State,
   s.School.ZIP,
   s.School.PhoneNo,
   s.School.Email,
   Principal = new {
      leader.FirstName,
      leader.LastName,
      leader.Email
   }
}

This results in a much smaller query:

SELECT
1 AS [C1],
[Extent2].[Address1] AS [Address1],
[Extent2].[Address2] AS [Address2],
[Extent2].[City] AS [City],
[Extent2].[State] AS [State],
[Extent2].[ZIP] AS [ZIP],
[Extent2].[PhoneNo] AS [PhoneNo],
[Extent2].[Email] AS [Email],
[Join2].[FirstName] AS [FirstName],
[Join2].[LastName] AS [LastName],
[Join2].[Email] AS [Email1]
   FROM [dbo].[Scholar] AS [Extent1]
   LEFT OUTER JOIN [dbo].[School] AS [Extent2] ON [Extent1].
[SchoolID] = [Extent2].[ID]
LEFT OUTER JOIN (SELECT [Extent3].[ID] AS [ID1],
[Extent4].[FirstName] AS [FirstName], [Extent4].
[LastName] AS [LastName], [Extent4].[Email] AS [Email]
   FROM [dbo].[Staff] AS [Extent3]
   INNER JOIN [dbo].[Person] AS [Extent4] ON [Extent3].
[ID] = [Extent4].[ID] ) AS [Join2] ON [Extent2].
[LeaderStaffID] = [Join2].[ID1]
WHERE 2764 = [Extent1].[ID]

Looking at the query execution plan in SSMS, the first query is roughly twice as expensive as the second, so not only is the query cleaner, but it performs and scales better as well.

A tool like Telerik JustTrace and ANTS Performance Profiler can be used here to discover the performance improvement because it lets you see the generated SQL from a LINQ to SQL/EF query.