Optimising Entity Framework - Don’t query from base entity

Be careful to query the derived type when dealing with an entity data model that involves inheritance. You can massively reduce query complexity by making sure you avoid querying the base type of your model.

Say there is a model with a base type of Person and three derived types of Customer, StaffMember, Client. It doesn't matter if your inheritance model is Table per Inheritance or Table per Type (TPT) but say for example it is Table per Hierarchy (TPH) and the condition for Customer is that an OrdersID column is not null. Also say there is a quality that of all Customers that they will have a guaranteed value of PersonType == 1.

So what is the difference between:

context.People.Where(p => p.MessageType == 1) and context.People.OfType()?

Aside from the better code the difference in generated SQL is massive. For some reason Entity Framework will generate SQL that works out which derived type each of the rows in the Person table are even though I'm asking for just the base type. This means unnecessarily joining on a number of tables if you’re using TPT or evaluating many conditional columns if you’re using TPH.

I tried the different approaches on quite a large Entity Data Model of mine. There is one base type with 5 derived types and a few other entities being included (joined). Using the first query from above the generated query was 28224 characters long. The revised query was 14177. That's half the size!

Using query length is a poor way of evaluating a query because most of EF queries are explicit query lengths which don’t actually add to complexity but believe me – in the case the saving of query length was almost exclusively down to removing pointless complexity.