Linq to Entities

linq-to-entities

I built an Entity Framework (v1) model which includes inheritance, like this:

Base class: Issue

<- Complaint

<- CustomerQuery
etc. (6 derived classes)

These classes map to corresponding tables in SQL Server.

I was impressed with the way that I could then use LINQ to entities to do creation – EF figures out that it must put some data in one table, get the identity key value and use it in the other table.

BUT: When I do a simple query such as the following

var result = dataContext.IssueSet.Where(x => x.Id == id);

… the ToTraceString reveals SQL code over 1000 lines long! And it performs awefully.

Do you think it is likely that I've done something wrong in my model or is the huge SQL code characteristic of inheritance structures in L2E?

P.S. Here's the beginning of the SQL trace string for info:

SELECT 
1 AS [C1], 
CASE WHEN (( NOT (([UnionAll8].[C45] = 1) AND ([UnionAll8].[C45] IS NOT NULL))) 
AND ( NOT (([UnionAll8].[C46] = 1) AND ([UnionAll8].[C46] IS NOT NULL))) 
AND ( NOT (([UnionAll8].[C47] = 1) AND ([UnionAll8].[C47] IS NOT NULL))) 
AND ( NOT (([UnionAll8].[C48] = 1) AND ([UnionAll8].[C48] IS NOT NULL))) 
AND ( NOT (([UnionAll8].[C49] = 1) AND ([UnionAll8].[C49] IS NOT NULL))) 
AND ( NOT (([UnionAll8].[C50] = 1) AND ([UnionAll8].[C50] IS NOT NULL))) 
AND ( NOT (([UnionAll8].[C51] = 1) AND ([UnionAll8].[C51] IS NOT NULL))) 
AND ( NOT

Best Answer

I don't think that query is simple at all. Consider what you are asking the framework to actually do: Materialize an entity which could be one of at least six different types. In fact, you probably know what type of entity you are trying to materialize; you just haven't given that information to the framework.

So, some thoughts:

  • When selecting entities of a single type from an entity set which includes multiple types, use OfType to inform the framework that you do not intend to materialize any other types.
  • Consider using table per hierarchy rather than table per type models, especially when most subtypes have many fields in common.
  • Within the constraints of good OOD, don't over-use inheritance. I find that many people tend to use inheritance for problems which would be better solved by composition, with or without the Entity Framework.
  • Be careful about writing off the Entity Framework simply because you had a problem on your first attempt to use it in you read something on a message board which gave you pause. The framework is complex, it works differently than many people expect, but it does work well when you understand it. Your first attempt at using it will probably not be correct. Stick with it and learn how the framework works.
Related Topic