Archives
-
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.
-
Small SQL improvement in Table Per Hierarchy
Well the title of my post is somewhat disguising the actual point which I wanted to communicate. There is basically two important things that i wanted to talk about in this posting. First a slight improvement in T-SQL generation for Table Per Hierarchy from version 1 of EF. Secondly an existing smart T-SQL generation for Table Per Hierarchy which had always been there but i never noticed it. Let’s dwell into some of these features by starting with an existing model that i have already created.