locked
Query problems on table-per-type inheritance Framework 3.5 RRS feed

  • Question

  • hello,

    i have two problems dealing with entity SQL / LINQ queries for table-per-type inheritance.

    i have a database with the following tables: customers, privateCustomers, corporateCustomers. i try to realize this custruct in entity framework with table-per-type inheritance. so privateCustomer an corporateCustomer are specialzations of the abstract class customer. until now everything works fine - when i create querys on customer (both, per entity SQL or LINQ) i have some privateCustomer objects an some corporateCustomer objects in my query result - wonderful.

    but now problems are occuring:
    1. how can i implement querys whith a where clause on the properties of the subclasses? e.g. i want to select all objects with special values in customer.PropertyA and corporateCustomer.PropertyB. I would prefer an solution for Entity SQL, because i want to create the query dynamicly, but corporateCustomer and privateCustomer seem not to be available on entity SQL and there properties are unknown für customer...
    2. how can i load associated objects for a mixed query result? e.g. when i get a query result containing several private and corporateCustomers i don't want to iterate all results and load the different assiciated objects. i would prefer Include()-Calls or something like that at the query, but i don't know how to realize this in mixed results...
    thanks in advance
    best regards
    Andreas
    Monday, August 10, 2009 12:57 PM

Answers

  • Hi Andreas,

    Problem 1:

    Take a look at the first link I wrote, in order to select a dervied class in ESQL, you need to use the OfType function, like so:
    SELECT VALUE customer FROM
        OFTYPE(CustomerEntities.Customer, CustomerEntities.PrivateCustomer) AS customer

    (the first parameter of the OfType is the name of the entity set, the second parameter is the name of the entity type

    Problem 2:
    Since you can't execute both queries at once for the order by, you'll need to be more creative - if you want to sort 100 customers and you have two lists which you don't know what they will look like after the sort - maybe the first 100 is a combination of both lists, maybe it will be 99 from the first and 1 from the second, just select 100 items from each list (total of 200 items) and then use the Local query to get both items to one list with an order by and use the Take method to take the first 100 customers. This way you're covered even if the 100 customers are only from one of the lists. As for performance, selecting N*2 rows is not considered a problem (unlike N^2 or N*M).
    Please mark posts as answers/helpful if it answers your question
    • Marked as answer by Yichun_Feng Monday, August 17, 2009 1:27 AM
    • Unmarked as answer by as79 Monday, August 17, 2009 2:04 PM
    • Marked as answer by Noam Ben-Ami - MSFT1 Tuesday, August 25, 2009 11:42 PM
    Friday, August 14, 2009 8:04 AM

All replies

  • no ideas?
    Tuesday, August 11, 2009 8:01 AM
  • 1. You can use the OfType function (works in L2E and Esql):
    http://msdn.microsoft.com/en-us/library/bb399295.aspx

    2. You can't add include calls on base types, they have to be called on the derived types (after using OfType).
    You can use 2 queries - one that uses OfType on the privateCustomers and uses Include for all of their properties and another query with OfType of corporateCustomers with its properties. as for the two result sets - both of them will exist in the context after the queries are executed, so you can use the ObjectStateManager to get all cutomers objects.
    http://blogs.msdn.com/dsimmons/archive/2009/02/21/local-queries.aspx
    Please mark posts as answers/helpful if it answers your question
    Wednesday, August 12, 2009 5:16 PM
  • Hi as79,

    For your first question, if you want to get the properties of the child classes from the father classes, it is not supported based on the Object-oriented programming. So it depends on how you want to query it, Ido’s reply works under some conditions.

     

    For your second question, you can add a column in base table to get the type of child classes. You can get idea form this example to add a condition value:

    http://msdn.microsoft.com/en-us/library/bb738443.aspx

     

    Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

    Thursday, August 13, 2009 5:03 AM
  • Hi,

    thanks for your hints but i'm still not able to solve my problems.

    Problem 1:

    Dealing with Entity SQL i'm not able to select the SubClass at all, because it's not known in the Context

    working mainclass-call
    SELECT VALUE Customer FROM CustomerEntities.Customer AS Customer

    not working subclass-calls
    SELECT VALUE Customer FROM CustomerEntities.PrivateCustomer AS Customer
    SELECT VALUE PrivateCustomer FROM CustomerEntities.Customer AS Customer

    i get the error message, that PrivateCustomer is unknown. I the code PrivateCustomer is known, but not as an propertie of the CustomerEntities.
    without request on the subclass i won't be able to add subclass-property-where clause

    how would a correct esql-statment would look like?

    Problem 2:

    the posted link deals with table-per-hirarchie genaralization and i'm using table-per-type...
    when i use multiple calls for the include of different references (like ido suggested) there would be another problem because when i want to select the first 100 customers, sorted by name (main-class property) - how can i merge the two result-sets in the right sorting and how should i know how much of each subtype-object-type must be selected because i don't know the percentage of each type in first 100 hits...

    so at the moment i don't see an other chance than iterate the result and reload after checking the type. but that wouln't be very performant...

    best regards
    andreas
    Friday, August 14, 2009 7:49 AM
  • Hi Andreas,

    Problem 1:

    Take a look at the first link I wrote, in order to select a dervied class in ESQL, you need to use the OfType function, like so:
    SELECT VALUE customer FROM
        OFTYPE(CustomerEntities.Customer, CustomerEntities.PrivateCustomer) AS customer

    (the first parameter of the OfType is the name of the entity set, the second parameter is the name of the entity type

    Problem 2:
    Since you can't execute both queries at once for the order by, you'll need to be more creative - if you want to sort 100 customers and you have two lists which you don't know what they will look like after the sort - maybe the first 100 is a combination of both lists, maybe it will be 99 from the first and 1 from the second, just select 100 items from each list (total of 200 items) and then use the Local query to get both items to one list with an order by and use the Take method to take the first 100 customers. This way you're covered even if the 100 customers are only from one of the lists. As for performance, selecting N*2 rows is not considered a problem (unlike N^2 or N*M).
    Please mark posts as answers/helpful if it answers your question
    • Marked as answer by Yichun_Feng Monday, August 17, 2009 1:27 AM
    • Unmarked as answer by as79 Monday, August 17, 2009 2:04 PM
    • Marked as answer by Noam Ben-Ami - MSFT1 Tuesday, August 25, 2009 11:42 PM
    Friday, August 14, 2009 8:04 AM
  • Hi Andreas,

    Ido's suggestion about using OfType can just solve your first problem.
    You can refer to this blog:
    http://blogs.msdn.com/bags/archive/2009/03/06/entity-framework-modeling-table-per-type-inheritance.aspx
    It will be helpful for your both problems.

    Best Regards

    Yichun Feng

    • Proposed as answer by Yichun_Feng Monday, August 17, 2009 1:28 AM
    Sunday, August 16, 2009 5:53 AM
  • Hello again,

    at first: thanks a lot for your responses!

    the recommended solution works, but there occured new problems which have to be resolved...

    problem 3:

    i didn't wrote it yet, but i have to skip some entries of the result for paging issues. so it's a pitty that i have to work with two requests because when i should select 100 entries and skip 100 entries, i have to select 200 CorporateCustomers and 200 PrivateCustomers, because it's possible, that all CCs are sorted before all PCs... when i think about growing number of customers it's necessary to solve this problem...

    problem 4:

    when are entityStates loaded into the context? after calling queryPrivate.Take<Privatperson>(numberOfSelectedEntities.Value + numberOfSkippedEntities.Value) they arn't available - so i added .ToArray<CorporateCustomer>(), but what is the fastest or "cheapest" way to load the entites into the context?

    problem 5:

    is int possible to have dynamic sortColumns when calling GetObjectStateEntries?


    the actual code looks like that:

    using (CustomerEntities context = new CustomerEntities ())
    {
        //Query for Corporate, where / orderby in queryString
        if (selectCorporate)
        {
            var queryCorporate = context.CreateQuery<CorporateCustomer>(queryStringCorporate, parametersCorporate).Include("CorporatePropertyA").Include("CustomerPropertyA").Include("CustomerPropertyB");

            if (selectCorporate && queryCorporate != null && !queryCorporate.Count<CorporateCustomer>().Equals(0))
                if (selectPrivate) //if both entities are needet, more ds are needed because of global skipping and sorting
                    queryCorporate.Take<CorporateCustomer>(numberOfSelectedEntities.Value + numberOfSkippedEntities.Value).ToArray<CorporateCustomer>();
                else
                    queryCorporate.Skip(numberOfSkippedEntities.Value).Take<CorporateCustomer>(numberOfSelectedEntities.Value).ToArray<CorporateCustomer>();
        }

        //Query for Private , where / orderby in queryString
        if (selectPrivate)
        {
            var queryPrivate = context.CreateQuery<PrivateCustomer>(queryStringPrivate, parametersPrivate)
                .Include("PrivateProperyA").Include("PrivateProperyB").Include("PrivateProperyC").Include("CustomerPropertyA").Include("CustomerPropertyB");

            if (queryPrivate != null && !queryPrivate.Count<PrivateCustomer>().Equals(0))
                if (selectCorporate) //if both entities are needet, more ds are needed because of global skipping and sorting
                    queryPrivate.Take<PrivateCustomer>(numberOfSelectedEntities.Value + numberOfSkippedEntities.Value).ToArray<PrivateCustomer>();
                else
                    queryPrivate.Skip(numberOfSkippedEntities.Value).Take<PrivateCustomer>(numberOfSelectedEntities.Value).ToArray<PrivateCustomer>();
        }

        //Contextquery including Sorting
        result = (from stateEntry in context.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged)
                  where stateEntry.Entity != null && stateEntry.EntitySet.Name == "Customer"
                  orderby ((Customer)stateEntry.Entity).AnzeigenAls, ((Customer)stateEntry.Entity).Name, ((Customer)stateEntry.Entity).ID
                  select stateEntry.Entity as Customer).Skip(numberOfSkippedEntities.Value).Take<Customer>(numberOfSelectedEntities.Value).ToList();
    }
     
    thank you very much in advance

    best regards
    Andreas
    Monday, August 17, 2009 2:02 PM
  • hello,

    i solved problem 3 by adding an objectquery-select which selects the ID column for Customers which fit the query when PrivateCustomer and CorporateCustomer are selected - i'm adding the result to the where clause of the PC and CC-Querys. if there is a where clause for an PC or CC-Property this extra select isn't needed, because in this case theres no need to select both sub-objects

    best regards
    Andreas
    Tuesday, August 18, 2009 8:19 AM
  • hallo,

    no suggestions for 4 and 5?

    problem 4:

    when are entityStates loaded into the context? after calling queryPrivate.Take<Privatperson>(numberOfSelectedEntities.Value + numberOfSkippedEntities.Value) they arn't available - so i added .ToArray<CorporateCustomer>(), but what is the fastest or "cheapest" way to load the entites into the context?

    problem 5:

    is int possible to have dynamic sortColumns when calling GetObjectStateEntries?


    best regards
    Andreas
    Tuesday, August 25, 2009 7:14 AM