Joins of child tables included in Select

Joins of child tables included in Select

Postby Nahmis » Thu 26 Nov 2009 13:45

We often have child objects we want to load at once for tabular display, 1 down from the parent, but the actual sql behavior isn't anywhere near optimal..I'm hoping this is something we're not understanding on the model side.

This is simplified for brevity, but if you need a full example I can post.
PrimaryBusinessAddress is an object of type Address, linked to from
Code: Select all
long? PrimaryBusinessAddressId

It's a nullable foreign key to Address.Id

Code: Select all
var orgs = from o in DB.Organizations
  select new {
    o.Id,
    o.Name,
    o.PrimaryBusinessAddress
  };


This results in:
Code: Select all
SELECT t1.ID AS "Id", t1.NAME AS "Name", t1.PRIMARY_BUSINESS_ADDRESS_ID
FROM I.ORGANIZATION t1


And this, repeated for every row:
Code: Select all
SELECT t1.ID, t1.LOCATION_DETAIL_ID, t1.ADDRESS_LINE1, t1.ADDRESS_LINE2, t1.ADDRESS_LINE3, t1.CITY, t1.ZIP_CODE, t1.ADDRESS_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.RECORD_STATUS, t1.CONTACT_NAME, t1.INSTITUTION_NAME, t1.DATE_START, t1.LOCATION_OBJECT_ID, t1.PROTOCOL_ID, t1.STATE, t1.COUNTRY, t1.TIME_ZONE
FROM I.ADDRESS t1
WHERE :np0 = t1.ID


What we'd expect is that this would all be 1 query with the child tables included in the select left outer joined. Is there anything we can do to get this?....or is our only option to manually join all child tables on every query?

I understand the lazy-load scheme, but if we're requesting it directly in anonymous type, it seems it should be left-joined to begin with. Any chance our model is just wrong somehow and this already works? (We are on 5.35.54)
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby halley73to » Thu 26 Nov 2009 13:55

Try to see DataLoadOptions
halley73to
 
Posts: 29
Joined: Wed 07 Oct 2009 07:24

Postby Nahmis » Thu 26 Nov 2009 13:58

We are using a data context at the HttpRequest level, so this isn't feasible.

Also in this case it results in a full join (because it is a FK, even though it is nullable, which I think is incorrect behavior), giving an invalid subset of the actual results.
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Fri 27 Nov 2009 11:13

Try to use the query-level LoadWith method. This should help.
This is an analogue to DataLoadOptions.LoadWith, but is applied on query level.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Nahmis » Fri 27 Nov 2009 11:33

If I change to this:
Code: Select all
var orgs = from o in DB.Organizations.LoadWith(org => org.PrimaryBusinessAddress)
  select new {
    o.Id,
    o.Name,
    o.PrimaryBusinessAddress
  };


I get the following exception/stack:
Code: Select all
Specified method is not supported.

[NotSupportedException: Specified method is not supported.]
   Devart.Data.Linq.Provider.Query.bk.a(SqlNode A_0) +2608
   Devart.Data.Linq.Provider.Query.bk.a(Expression A_0) +81
   Devart.Data.Linq.Provider.Query.bk.d(Expression A_0, Expression A_1) +127
   Devart.Data.Linq.Provider.Query.bk.b(MethodCallExpression A_0) +2395
   Devart.Data.Linq.Provider.Query.bk.j(Expression A_0) +403
   Devart.Data.Linq.Provider.Query.bk.a(LambdaExpression A_0) +1284
   Devart.Data.Linq.Provider.Query.bk.j(Expression A_0) +771
   Devart.Data.Linq.Provider.Query.bk.i(Expression A_0) +102
   Devart.Data.Linq.Provider.DataProvider.a(Expression A_0) +295
   Devart.Data.Linq.Provider.DataProvider.i(Expression A_0) +162
   Devart.Data.Linq.CompiledQuery.a(DataContext A_0, Object[] A_1) +260
   Devart.Data.Linq.CompiledQuery.Invoke(a A_0) +141
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Mon 30 Nov 2009 16:04

Could you please send me (support * devart * com, subject "LINQ: CompiledQuery") a small test project illustrating the problem with CompiledQuery?
Also please specify the version of dotConnect for Oracle you are using.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Eliminating CompiledQuery

Postby Nahmis » Wed 09 Dec 2009 01:34

Eliminating compiled query from the equation, here's what's happening:

When we perform the following query (DataContext.New just gets a new DataContext, rigs up some debugging tracking):

Code: Select all
var dc = DataContext.New;
var dlo = new DataLoadOptions();
dlo.LoadWith(gs => gs.PrimaryBusinessAddress);
dlo.LoadWith(gs => gs.PrimaryBusinessPhone);
dlo.LoadWith(gs => gs.PrimaryEmail);
dc.LoadOptions = dlo;

var query = from gs in dc.GlobalSites
               select gs;

We get this:
Code: Select all
SELECT t1.ID, t1.SITE_NAME, t1.WEBSITE, t1.SITE_TIMEZONE, t1.COMMENTS, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.INFOLINK_ID, t1.RECORD_STATUS, t1.VISIBLE_PROTOCOL_ID, t1.PRACTICE_TYPE, t1.DATE_START, t1.PRIMARY_EMAIL_ID, t1.PRIMARY_BUSINESS_ADDRESS_ID, t1.PRIMARY_BUSINESS_PHONE_ID, t1.PRIMARY_CELLULAR_ID, t1.PRIMARY_FAX_ID
FROM (
    SELECT t2.ID, t2.SITE_NAME, t2.WEBSITE, t2.SITE_TIMEZONE, t2.COMMENTS, t2.OWNER, t2.LAST_USER, t2.MODIFIED_REASON, t2.DATE_CREATED, t2.DATE_MODIFIED, t2.VERSION, t2.ENTITY_ID, t2.INFOLINK_ID, t2.RECORD_STATUS, t2.VISIBLE_PROTOCOL_ID, t2.PRACTICE_TYPE, t2.DATE_START, t2.PRIMARY_EMAIL_ID, t2.PRIMARY_BUSINESS_ADDRESS_ID, t2.PRIMARY_BUSINESS_PHONE_ID, t2.PRIMARY_CELLULAR_ID, t2.PRIMARY_FAX_ID, ROW_NUMBER() OVER (ORDER BY t2.SITE_NAME) AS "rnum"
    FROM (
        SELECT t3.ID, t3.SITE_NAME, t3.WEBSITE, t3.SITE_TIMEZONE, t3.COMMENTS, t3.OWNER, t3.LAST_USER, t3.MODIFIED_REASON, t3.DATE_CREATED, t3.DATE_MODIFIED, t3.VERSION, t3.ENTITY_ID, t3.INFOLINK_ID, t3.RECORD_STATUS, t3.VISIBLE_PROTOCOL_ID, t3.PRACTICE_TYPE, t3.DATE_START, t3.PRIMARY_EMAIL_ID, t3.PRIMARY_BUSINESS_ADDRESS_ID, t3.PRIMARY_BUSINESS_PHONE_ID, t3.PRIMARY_CELLULAR_ID, t3.PRIMARY_FAX_ID
        FROM I22.GLOBAL_SITE t3
        WHERE (:p0 <> 0) AND (t3.RECORD_STATUS = :p1) AND (t3.ENTITY_ID = :p2)
        ) t2
    ORDER BY t2.SITE_NAME
    ) t1
WHERE (t1."rnum" > :p3) AND (t1."rnum" <= :p4)


Almost equivalent result as with query level load with options (seems to completely ignore LoadWith?):
Code: Select all
from gs in DB.GlobalSites.LoadWith(gs => gs.PrimaryBusinessAddress).LoadWith(gs => gs.PrimaryBusinessPhone).LoadWith(gs => gs.PrimaryEmail)
select gs;

Code: Select all
SELECT t1.ID, t1.SITE_NAME, t1.WEBSITE, t1.SITE_TIMEZONE, t1.COMMENTS, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.INFOLINK_ID, t1.RECORD_STATUS, t1.VISIBLE_PROTOCOL_ID, t1.PRACTICE_TYPE, t1.DATE_START, t1.PRIMARY_EMAIL_ID, t1.PRIMARY_BUSINESS_ADDRESS_ID, t1.PRIMARY_BUSINESS_PHONE_ID, t1.PRIMARY_CELLULAR_ID, t1.PRIMARY_FAX_ID
FROM (
    SELECT t2.ID, t2.SITE_NAME, t2.WEBSITE, t2.SITE_TIMEZONE, t2.COMMENTS, t2.OWNER, t2.LAST_USER, t2.MODIFIED_REASON, t2.DATE_CREATED, t2.DATE_MODIFIED, t2.VERSION, t2.ENTITY_ID, t2.INFOLINK_ID, t2.RECORD_STATUS, t2.VISIBLE_PROTOCOL_ID, t2.PRACTICE_TYPE, t2.DATE_START, t2.PRIMARY_EMAIL_ID, t2.PRIMARY_BUSINESS_ADDRESS_ID, t2.PRIMARY_BUSINESS_PHONE_ID, t2.PRIMARY_CELLULAR_ID, t2.PRIMARY_FAX_ID, ROW_NUMBER() OVER (ORDER BY t2.SITE_NAME) AS "rnum"
    FROM I22.GLOBAL_SITE t2
    WHERE (:p0 <> 0) AND (t2.RECORD_STATUS = :p1) AND (t2.ENTITY_ID = :p2)
    ORDER BY t2.SITE_NAME
    ) t1
WHERE (t1."rnum" > :p3) AND (t1."rnum" <= :p4)


Followed by a whole lot of this (n times, based on rows shown):
Code: Select all
SELECT t1.ID, t1.EMAIL_ADDRESS, t1.EMAIL_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.SHARED_EMAIL_ADDRESS, t1.RECORD_STATUS, t1.DATE_START, t1.PROTOCOL_ID
FROM I22.EMAIL t1
WHERE :np0 = t1.ID
ParameterName = np0
DbType = Decimal
Value =

SELECT t1.ID, t1.LOCATION_DETAIL_ID, t1.PHONE_NUMBER, t1.PHONE_NUMBER_EXTENSION, t1.PHONE_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.DATE_START, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.RECORD_STATUS, t1.PROTOCOL_ID
FROM I22.PHONE t1
WHERE :np0 = t1.ID
ParameterName = np0
DbType = Decimal
Value = 4759

SELECT t1.ID, t1.LOCATION_DETAIL_ID, t1.ADDRESS_LINE1, t1.ADDRESS_LINE2, t1.ADDRESS_LINE3, t1.CITY, t1.ZIP_CODE, t1.ADDRESS_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.RECORD_STATUS, t1.CONTACT_NAME, t1.INSTITUTION_NAME, t1.DATE_START, t1.LOCATION_OBJECT_ID, t1.PROTOCOL_ID, t1.STATE, t1.COUNTRY, t1.TIME_ZONE
FROM I22.ADDRESS t1
WHERE :np0 = t1.ID
ParameterName = np0
DbType = Decimal
Value = 8391


The Association properties all look like this:
Code: Select all
[Association(Name="Address_GlobalSite", Storage="_PrimaryBusinessAddress", ThisKey="PrimaryBusinessAddressId", IsForeignKey=false)]
public Address PrimaryBusinessAddress

[Association(Name="Phone_GlobalSite", Storage="_PrimaryBusinessPhone", ThisKey="PrimaryBusinessPhoneId", IsForeignKey=false)]
public Phone PrimaryBusinessPhone

[Association(Name="Email_GlobalSite", Storage="_PrimaryEmail", ThisKey="PrimaryEmailId", IsForeignKey=false)]
public Email PrimaryEmail


The properties are many to 1, since an address may have multiple sites for which it is primary...is there any way we can correctly get GlobalSites to load with these child properties populated without either a) Joining manually in every query where we need them (leaving us with a sometimes undesirable anonymous type), or b) incurring a independent query for each child property for every object we deal with?

This is what we currently do to get a valid result (The performance on this also leaves something to be desired, although the actual query is around 20ms, the actual time spent in dotConnect populating the object is around 350ms, anything we can do about this as well?):
Code: Select all
var globalsites = from gs in DB.GlobalSites
         join add in DB.Address on gs.PrimaryBusinessAddresses equals add into gsa
         join phone in DB.Phones on gs.PrimaryBusinessPhone equals phone into gsp
         join email in DB.Emails on gs.PrimaryEmail equals email into gse
         from a in gsa.DefaultIfEmpty()
         from p in gsp.DefaultIfEmpty()
         from e in gse.DefaultIfEmpty()
         select new {
               gs.Id,
               gs.RecordStatus,
               gs.EntityId,
               gs.DateModified,
               gs.SiteName,
               PrimaryBusinessAddress = a,
               PrimaryBusinessPhone = p,
               PrimaryEmail = e
               };
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Thu 10 Dec 2009 11:38

We are able to implement the result of LoadWith as a join of tables only in the case of two-level association
(like Microsoft does). The associations of level higher than 2 will be selected as a join of first two levels
and a number of selects from the next level, what is illustrated by your example.
As for the question - the a) option is reasonable in case when b) is unacceptable.
Any other options are not available yet. We will investigate this situation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Nahmis » Thu 10 Dec 2009 17:41

The associations of level higher than 2 will be selected as a join of first two levels
and a number of selects from the next level, what is illustrated by your example.


I don't think this is correct...look at my example, nothing at all is joined. The parent table is selected, and then all 1:1 direct children are selected independently with separate queries. This can all be done in a single query, I'm not sure why it's not possible to join all the 1:1 relationships automatically if they're in the asked for in the select. We are not joining more than 2 levels, we're just joining multiple 1:1 relationships, only 2 levels exist, GlobalSite -> Single Child in each case.

A side note while we investigated this, a new bug:
dotConnect in the latest release (since 5.25) selects all children regardless of the possibility they exist. To clarify with an example, look at what I posted before...this is the first 3 queries after the GlobalSite one, selecting the first GlobalSite's 3 child objects with 1 select each:
Code: Select all
SELECT t1.ID, t1.EMAIL_ADDRESS, t1.EMAIL_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.SHARED_EMAIL_ADDRESS, t1.RECORD_STATUS, t1.DATE_START, t1.PROTOCOL_ID
FROM I22.EMAIL t1
WHERE :np0 = t1.ID
ParameterName = np0
DbType = Decimal
Value =


I didn't remove the value...it's querying even when GlobalSite.PrimaryEmailId is null (it's a Nulable)...this results in a ton of queries with no chance of a result...this was not the behavior when we tested this in 5.25.
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Fri 11 Dec 2009 14:38

Thank you for the report, the problem with null association endings is already fixed - they are not queried in the latest build.
We will investigate the possibility to implement joining of one-to-one related tables.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby lancelotti » Tue 09 Mar 2010 23:58

Any progress with this?
Thanks
lancelotti
 
Posts: 16
Joined: Tue 23 Feb 2010 18:28

Postby AndreyR » Thu 11 Mar 2010 10:03

Yes, we plan to implement joining of one-to-one tables in LoadWith regardless to the level it is nested in.
Unfortunately, I can't provide any timeframe.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle