Code First EF 6 Table Per Hierarchy SQL query generation issue

Question

I have a model with several classes with inheritance. I have a 4-level hierarchy:

--First level abstrac class A
  --Second level abstrac class AA
    --Third level:
     *abstract class AAA
     *abstract class AAB
     *abstract class AAC
     *class AAD
     *class AAE
     *abstract class AAF
     *class AAG
     *class AAH
     *abstract class AAI
       --Fourth level:
        *class AAA1
        *class AAA2
        *class AAA3
        *class AAB1
        *class AAB2
        *class AAB3
        *class AAC1
        *class AAC2
          ...
        *class AAF1
        *class AAF2
        *class AAF3
        *class AAF4
        *class AAF5

This is just one of the hierarchies. There be at least 3 more, which are less complex. They have several relationships between them, and almost all classes have more than 7 properties.

Now, I am using Table per Type but when I do a simple LINQ query like:

        using (Logic.Context dc = new Logic.Context())
        {
            var prod = dc.AA.FirstOrDefault();
        }

AA contains all the subclasses. When database is empty, it takes around 25 secs to do this simple test . It takes just 2 secs to do the same LINQ query with a fourth-level class.

I have tried to migrate to Table per Hierarchy, the database is generated well by default with a column Discriminator but the same test takes forever... The LINQ query is never executed, and it doesn't even completed. And this happens with all tables.

Any suggestion?

score 0 · Answer 1 · answered Jan 04 '14 at 00:03

My theory is that it's taking forever to just do FirstOrDefault because the context doesn't know which entity is 'First'

It's basically asking itself: First AA? Get entity 1 - which is... ( 25 seconds go by ) oh it's AAH! Here you go!

Try projecting your query onto an object that can get all properties from just AA

For example if AA is

class AA 
{
   public Int32 Id {get;set;}
   public String Name {get;set;}
   public DateTime Created {get;set;}
}

then your query might run faster if you do

var prod = dc.AA.Select( a => new 
{ 
    Id = a.Id, 
    Name = a.Name, 
    Created = a.Created 
}).FirstOrDefault();

Then in theory perhaps EF won't concern itself with what the first entity actually is.

But..

While I'm here - I just refactored all the TPT out of my project because of EF's CASE/JOIN hell.
I know it feels right and lovely to play with SQL Inheritance like this but it can and will burn you when you have to look at an AA instance and find out what it really is.

Well, I've just leave the idea of TPH and stayed with TPT. To avoid the 25 secs of wait, I just avoid AA. In my project I do all with reflection to avoid the table AA and the results are nice, but is a bit more dificult to program. With reflection I query all items of AA in 3 or 4 secs. — Marcos González, Jan 10 '14 at 16:01

Code First EF 6 Table Per Hierarchy SQL query generation issue

1 Answers1