Entity Framework, Table Per Type and Linq - Getting the "Type"

Question

I have an Abstract type called Product, and five "Types" that inherit from Product in a table per type hierarchy fashion as below:

I want to get all of the information for all of the Products, including a smattering of properties from the different objects that inherit from products to project them into a new class for use in an MVC web page. My linq query is below:

     //Return the required products
    var model = from p in Product.Products
                where p.archive == false && ((Prod_ID == 0) || (p.ID == Prod_ID))
                select new SearchViewModel 
                    {
                        ID = p.ID,
                        lend_name = p.Lender.lend_name,
                        pDes_rate = p.pDes_rate,
                        pDes_details = p.pDes_details,
                        pDes_totTerm = p.pDes_totTerm,
                        pDes_APR = p.pDes_APR,
                        pDes_revDesc = p.pDes_revDesc,
                        pMax_desc = p.pMax_desc,
                        dDipNeeded = p.dDipNeeded,
                        dAppNeeded = p.dAppNeeded,      

                        CalcFields = new DAL.SearchCalcFields
                        {
                            pDes_type = p.pDes_type,
                            pDes_rate = p.pDes_rate,
                            pTFi_fixedRate = p.pTFi_fixedRate 
                        }
                    }

The problem I have is accessing the p.pTFi_fixedRate, this is not returned with the Products collection of entities as it is in the super type of Fixed. How do I return the "super" type of Products (Fixed) properties using Linq and the Entity Framework. I actually need to return some fields from all the different supertypes (Disc, Track, etc) for use in calculations. Should I return these as separate Linq queries checking the type of "Product" that is returned?

Answer 1

This is a really good question. I've had a look in the Julie Lerman book and scouted around the internet and I can't see an elegant answer.

If it were me I would create a data transfer object will all the properties of the types and then have a separate query for each type and then union them all up. I would insert blanks into the DTO properies where the properties aren't relevant to that type. Then I would hope that the EF engine makes a reasonable stab at creating decent SQL.

Example

var results = (from p in context.Products.OfType<Disc>
        select new ProductDTO {basefield1 = p.val1, discField=p.val2, fixedField=""})
         .Union(
        from p in context.Products.OfType<Fixed>
        select new ProductDTO {basefield1 = p.val1, discField="", fixedField=p.val2});

But that can't be the best answer can it. Is there any others?

Answer 2

So Fixed is inherited from Product? If so, you should probably be querying for Fixed instead, and the Product properties will be pulled into it.

If you are just doing calculations and getting some totals or something, you might want to look at using a stored procedure. It will amount to fewer database calls and allow for much faster execution.

Answer 3

Well it depends on your model, but usually you need to do something like:

var model = from p in Product.Products.Include("SomeNavProperty")
.... (rest of query)

Where SomeNavProperty is the entity type that loads pTFi_fixedRate.