Joins of child tables included in Select

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Joins of child tables included in Select

Post by 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)

halley73to
Posts: 29
Joined: Wed 07 Oct 2009 07:24

Post by halley73to » Thu 26 Nov 2009 13:55

Try to see DataLoadOptions

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Post by 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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Eliminating CompiledQuery

Post by 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"  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
					};

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

lancelotti
Posts: 16
Joined: Tue 23 Feb 2010 18:28

Post by lancelotti » Tue 09 Mar 2010 23:58

Any progress with this?
Thanks

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Post Reply