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.

To query the above model for all the Person in our database, we can use the ObjectSet People exposed on our ObjectContext. Code below shows an example.

To see the SQL improvement from version 1 to version 4 of EF, I ran the project first against .net framework 3.5 and then against .net 4.0. Code below shows both the SQL capture using SQL Profiler.

On the above code notice that EF is building OR clause to bring both customer and employee from People table. However the improvement or cleaner syntax in EF4 is use of contains clause. Another cool feature about the above T-SQL generation which i never noticed is its smart enough to apply the filter to the People table to only bring in Customer and Employee. I originally thought that it was probably bringing all the records from the database and then on the client side, it looks at the model and determines that it only needs to care about customer and Employee and therefore discards rest of the records. Instead it finds all the derived type’s condition and applies that as the where clause to the People table. This way you only bring down records that can actually be consumed by ObjectSet of People.

What i have not tried is what happens if you have nested derive types that adds sub conditions or even overrides the base conditions, what about cases where you have multiple conditions and then one condition being overridden on sub derive types. I let reader try out those scenarios to see if they actually work as well or not.