Optimize Linq To Entities queries using let clauses

This post dates back to 2012 and is migrated from my old blog.

In this article I will show you that let clauses inside Linq queries are not a simple matter of readability of the query, when it comes to using Entity Framework and the Linq to Entities provider.
In some cases, in fact, they can provide a little bit of optimization of the SQL query generated.
In my example I suppose that you already used Entity Framework Code First.
I create a DbContext with a single Dbset of Person entities:

public class PersonContext : DbContext
{
  public DbSet<Person> Persons { get; set; }
}

I create a Person entity and an Address entity which is a child entity of Person:

public class Person
{
  public long PersonId { get; set; }

  public string Name { get; set; }

  public string Surname { get; set; }

  public Address Address { get; set; }
}

public class Address
{
  public long AddressId { get; set; }

  public string Town { get; set; }

  public string Street { get; set; }
}

The Address type is an entity and not a complex type because it has an AddressId property defined.
With two entities in place Entity Framework Code First will generate two tables via the DatabaseInitializer configured.
What I want to show is that if I want to select some Persons filtering on their Town and Street using a Linq query, I could and up sending to the Database a couple of joins between Person and Address instead of one, unless I use let clauses.

Let’s create a test or a Console application with the following code:

try
{
   using (var context = new PersonContext())
   {
   var initializer = new DropCreateDatabaseAlways<PersonContext>();
   initializer.InitializeDatabase(context);

   context.Persons.Add(new Person { Surname = "Smith" });
   context.SaveChanges();

   var result = from person in context.Persons
                 where person.Address.Street == "A" &&
                   person.Address.Town == "B"
    	           select person;
   var list = result.ToList();

   result = from person in context.Persons
   	      let address = person.Address
          where address.Street == "A" &&
                address.Town == "B"
          select person;
   list = result.ToList();
   }
}
catch (Exception exception)
{
   Console.WriteLine(exception.ToString());
}

In the preceding code snippet we explicitly invoke a DatabaseInitializer to be sure to create a freshly new database. We insert a Person entity (but we could have omitted this part for the sake of the example). Then we execute the same query once with a let clause and once without.

If you run Sql Server Profiler to see the queries sent to the Database and run your test or application you will see the following two SQL queries.
The first one is the result of the Linq query without the let clause:

SELECT 
1 AS [C1], 
[Filter1].[PersonId] AS [PersonId], 
[Filter1].[Name] AS [Name], 
[Filter1].[Surname] AS [Surname], 
[Filter1].[Address_AddressId] AS [Address_AddressId]
FROM   (SELECT [Extent1].[PersonId] AS [PersonId], [Extent1].[Name] AS [Name], [Extent1].[Surname] AS [Surname], [Extent1].[Address_AddressId] AS [Address_AddressId]
  FROM  [dbo].[People] AS [Extent1]
  INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_AddressId] = [Extent2].[AddressId]
  WHERE N'A' = [Extent2].[Street] ) AS [Filter1]
INNER JOIN [dbo].[Addresses] AS [Extent3] ON [Filter1].[Address_AddressId] = [Extent3].[AddressId]
WHERE N'B' = [Extent3].[Town]

The second one gives the result we would have coded if we wrote the SQL directly.

SELECT 
1 AS [C1], 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Surname] AS [Surname], 
[Extent1].[Address_AddressId] AS [Address_AddressId]
FROM  [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_AddressId] = [Extent2].[AddressId]
WHERE (N'A' = [Extent2].[Street]) AND (N'B' = [Extent2].[Town])

We can conclude that everytime we use more than once a navigation property inside of a Linq query against the Linq To Entities provider we’d better store the navigation property in a let clause and use the so defined variable instead.