Entity Framework table per type - select from only the base type columns

Question

We are using EF 4.3 Code first and have an object model like so:

class Content {  }

class Product:Content { }

class News:Content { }

These are mapped as Table per Type.

There are scenarios where I just want to load only the columns belonging to the base table, like say a list of all the content titles. But a query like

from c in Content
where c.IsDeleted == false
select c

results in some really nasty SQL with joins to the other two tables. Is there any way to force EF to just do a select from the base table only without joins to the other tables?

Leukocyte

Answer 1

TPT is problematic and EF generated queries are usually very inefficient. Moreover your expectations are probably incorrect. Linq-to-entities always returns the real type of entity. It cannot return instance of Content type if the record is in fact a Product entity. Your query can have only two meanings:

• Return all non deleted contents - this must perform joins to correctly instantiate a real types of entities. The query will return enumeration of Content, Product and News instances.
• Return all non deleted Content instances - this must probably again perform joins to correctly instantiate only records mapped to Content directly (without relation to Product and News). No record mapped to Product or News will be returned in the enumeration. This query is not possible with Linq-to-entities - you need to use ESQL and OFTYPE ONLY operator.

There are few things you can try:

• Upgrade to .NET 4.5 - there are some improvements for TPT queries
• Return projection of properties instead of Content - Product and News are also content so you will never get query without joins if you return Content instances from Linq-to-entities query

Rock

Thanks a bunch for your response. Upgrade to 4.5 is out of the question for the current project we are working on which is supposed to go live next month. We are planning to do such queries with stored procs. – Leukocyte