Optimize Linq To Entities queries using let clauses

18 12 2012

This post is no longer maintained. Click here for the updated version of this post

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 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:

   using (var context = new PersonContext())
	 var initializer = new DropCreateDatabaseAlways();

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

	 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)

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:

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.

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.





One response

22 09 2014
KSW 28 transmisja

I know this web site provides quality depending articles or
reviews and additional stuff, is there any other web page which gives such information in quality?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: