DevForce Community Forum : Excessively large SQL with inheritance model

Excessively large SQL with inheritance model : Many thanks. Looks like injected...

Thu, 02 Feb 2012 13:38:56 -700

Author: paul.gilbert
Subject: 3246
Posted: 02-Feb-2012 at 1:38pm

Many thanks. Looks like injected base types will do the trick to give me the hierarchy that's needed, and avoid the issues I was experiencing.

Excessively large SQL with inheritance model : Hi Paul, DevForce does not...

Wed, 01 Feb 2012 10:44:56 -700

Author: kimj
Subject: 3246
Posted: 01-Feb-2012 at 10:44am

Hi Paul,

DevForce does not actually generate the SQL for a LINQ query, we delegate that to the Entity Framework. There are known issues with inheritance in EF, specifically with TPT style inheritance. Here's one article - http://blogs.msdn.com/b/adonet/archive/2010/08/17/performance-considerations-when-using-tpt-table-per-type-inheritance-in-the-entity-framework.aspx - but if you do a web search on EF inheritance you'll find many more.

If you're using TPT inheritance and have control over the database, one option would be to instead use TPH or TPC style.

If all you need inheritance for is behavior, you can do that in DevForce with an "injected base type". Here's more information - http://drc.ideablade.com/xwiki/bin/view/Documentation/model-custom-base-class.

Excessively large SQL with inheritance model : I seem to be having some real...

Tue, 31 Jan 2012 22:46:37 -700

Author: paul.gilbert
Subject: 3246
Posted: 31-Jan-2012 at 10:46pm

I seem to be having some real trouble with the SQL being generated for my first major project using DevForce in C#. It's mandated that an inheritance hierarchy be used, with a common base entity class that contains a unique entity Id and various change tracking information, as well as a couple of intermediate base classes for differentiating between 'system' tables and standard tables (along with various extra properties).

Now, however, as I start adding tables to my EDMX and wiring up the inheritence, I'm starting to get problems with excessively large SQL being generated, which rapdily becomes an OutOfMemoryException as more tables are added. It seems to particularly manifest if I do an Include, to include a related table information, or do a standard Where query, as in 'BaseEntities.OfType.Where(...)'.

What I can see from the SQL being generated, is that DevForce seems to be doing 'LEFT OUTER JOINS' from the base entity class to every since table defined in the model, which is obviously not what I want. With the 'Where' I'm explicitly telling it that I want objects of the given child class type, so I would have thought the generated SQL would be straight inner joins from the base entity class down through the intermediate class to the child class, and only then do left outer joins to any client classes (if any).

Likewise, for the Include, I had assumed that the fact that the navigation property points to the specific given table, that any generated query wouldn't need to be doing outer joins to other tables elsewhere in the model.

Is anyone aware of something I may be doing wrong in the setup of my model and/or project that could cause this? Or is there a way that DevForce is meant to be used when dealing with an inheritance hierarchy to avoid this? It's pretty much mandated that the base classes be there, so I'm hoping it's just something simple that I'm doing wrong, and won't have to do anything major to try and work around.

Any suggestions appreciated.