Watch out for let operator in linq to entities

LINQ has a really cool operator call Let that lets you declare variables inside the query. The benefit i find in using let is, it makes my query much easier to read because using Let keyword i can create a variable and assign it a calculated value. Then later in my query i can test my value against that variable. In LINQ to entities if you are using let keyword, you want to be aware of how the sql is generated. There are cases where let keyword introduces extra or repetitive joins that are un-needed. Let’s see an example of where let introduces extra join when the linq query is translated. In the figure below I have a SalesRep entity that has an optional 0..1 association with RepSalesStatus. RepSalesStatus table in the database contains the totalsales a given rep has done. Since the association is optional there could be reps in our database that have not done any sales as yet.

Problem:  You want to return all SalesRep in your database regardless if they have ever done any sales or not. You want the sales rep to be sorted by the amount of total sales they have done.

Solution:

Since we want to show all sales reps regardless if they have any entry in the RepSalesStatus table, we need to do left join to the RepSalesStatus table. If a rep does not have any sales, we need to give it a default sale of 0. We can then sort all sales agent by the total sales they have done in descending order. Code below shows the code.

The above code works like  a charm but if you open up the profiler, it generates a bit of ugly sql. Code below shows the sql.

Notice the above sql does left join to RepSalesStatus table 3 times when that join should be done only once. The left join is repeating because we are using let operator with the 0..1 navigation property, RepSalesStatus. To avoid this problem we can use the join keyword to explicitly do a left join and this would ensure that we get a clean sql statement generated. Code below shows the linq query.

Figure below shows the sql captured for the above linq query.

So anytime you use let keyword, just make sure the sql query generated is optimized or not. Like the case i show above, sometimes when let is used with other associations, it does not generate optimal sql. You have a workaround at your disposal as shown in this post.