Query problems on table-per-type inheritance Framework 3.5

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

Answer 1

no ideas?

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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