entity-frameworkinheritancetable-per-hierarchytable-per-type

EF1: Filtering derived types of entity class using .OfType<> by passing a string value


I have a situation where I'm trying to filter a LINQ select using a derived sub class.

ctx.BaseEntity.OfType<SubClass>() - this works fine.

However I'd like to do this using a string value instead. I've come across a performance barrier when I have lots (>20) Sub Classes and selecting an Entity without using OfType just isn't an option. I have a generic UI that renders from the base class, so I don't know what Class Type will be returned at compile time.

So what I'd like to do is this:

  1. Perform a projected Select where I return just the SubClassType from the database
  2. Perform a second select using this value as the OfType to only select the relevant related entity from the database (No mass unions generated)

        int id = 1;
        var classType = (from c in ctx.BaseClass.Include("ClassType")
                                   where c.id == id
                                   select new
                                              {
                                                  c.ClassType.TypeName
                                              }).First();
    
        BaseClass caseQuery = ctx.BaseClass.OfType<classType.TypeName>()
                        .Include("ClassType")
                        .Include("ChildEntity1")
                        .Include("ChildEntity2")
                        .Where(x => x.id== id);
    

But obviously this won't work because OfType requires a Type and not a string.

Any ideas on how I can achieve this?

Update: As a side note to the original question, it turns out that the moment you project a query that uses a Navigation Property - it builds the monster SQL too, so I've ended up using a stored procedure to populate my ClassType entity from the BaseClass Id.


Solution

  • So I've just got it to work using eSQL, which I'd never used before. I've posted the code here just in case it helps someone. Has anyone else got a more strongly typed solution they can think of?

    BaseClass caseQuery = ctx.BaseClass.CreateQuery<BaseClass>("SELECT VALUE c FROM OFTYPE(Entities.[BaseClass],namespace.[" + classType.TypeName + "])  as c")
                    .Include("ClassType")
                    .Include("ChildEntity1")
                    .Include("ChildEntity2")
                    .Where(x => x.id== id).FirstOrDefault();