none
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?

    Wednesday, December 18, 2013 2:54 PM

Answers

  • Hello,

    >>The LINQ query is never executed, and it doesn't even completed. And this happens with all tables.

    Please have a try to use codes like below to check whether the query has been excuted:

    using (FourLevelContext db = new FourLevelContext())
    
                {
    
                    if (!db.Database.Exists())
    
                    {
    
                        db.Database.Create();
    
                    }
    
    
                    AAAA resultA = db.AA.OfType<AAAA>().FirstOrDefault<AAAA>();
    
    
                    Console.WriteLine(resultA.ID);
    
                    Console.WriteLine(resultA.FirstLevelName);
    
                    Console.WriteLine(resultA.SecondLevelName);
    
                    Console.WriteLine(resultA.ThirdLevelName);
    
                    Console.WriteLine(resultA.FourthLevelNameA);
    
    
                    AAAB resultB = db.AA.OfType<AAAB>().FirstOrDefault<AAAB>();
    
    
                    Console.WriteLine(resultB.ID);
    
                    Console.WriteLine(resultB.FirstLevelName);
    
                    Console.WriteLine(resultB.SecondLevelName);
    
                    Console.WriteLine(resultB.ThirdLevelName);
    
                    Console.WriteLine(resultB.FourthLevelNameB);
    
                }
    

    In my test, the query actually has been executed and the result has be returned:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 19, 2013 7:51 AM