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
};