I'm trying to create a fairly complex query with the Oracle EF framework (Oracle.ManagedDataAccess.EntityFramework nuget package, version 12.1.2400) with numerous left joins, utilizing the .DefaultIfEmpty() syntax:
from i in dbHR.Identities
join p in dbHR.Personals
on new {
key1 = i.ID,
key2 = true,
key3 = true
} equals new {
key1 = p.EID_ID,
key2 = (DbFunctions.TruncateTime(p.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0),
key3 = (DbFunctions.TruncateTime(p.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0)
}
into pj from p in pj.DefaultIfEmpty()
join s in dbHR.States on p.DSP_ID_ADDRESS equals s.ID into sj from s in sj.DefaultIfEmpty()
join e in dbHR.Employments
on new {
key1 = i.ID,
key2 = true
}
equals new {
key1 = e.EID_ID,
key2 = (!e.TERMINATION_DATE.HasValue || DbFunctions.TruncateTime(e.TERMINATION_DATE.Value).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0)
}
into ej from e in ej.DefaultIfEmpty()
join a in dbHR.Assignments
on new {
key1 = e.ID,
key2 = true
} equals new {
key1 = a.EEM_ID,
key2 = ((!a.ASSIGNMENT_END_DATE.HasValue || DbFunctions.TruncateTime(a.ASSIGNMENT_END_DATE.Value).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0)
&& (DbFunctions.TruncateTime(a.ASSIGNMENT_START_DATE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0)
&& (a.PRIME_ASSIGNMENT != null))
}
into aj from a in aj.DefaultIfEmpty()
join ad in dbHR.AssignmentDetails
on
new { key1 = a.ID, key2 = true }
equals
new
{
key1 = ad.EAS_ID,
key2 = (
DbFunctions.TruncateTime(ad.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
&& DbFunctions.TruncateTime(ad.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
)
}
into adj from ad in adj.DefaultIfEmpty()
join j in dbHR.Jobs
on ad.DJB_ID equals j.ID
into jj from j in jj.DefaultIfEmpty()
join jd in dbHR.JobDetails
on new {
key1 = j.ID, key2 = true
} equals new {
key1 = jd.DJB_ID,
key2 =
(
DbFunctions.TruncateTime(jd.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
&& DbFunctions.TruncateTime(jd.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
)
}
into jdj from jd in jdj.DefaultIfEmpty()
join d in dbHR.Departments
on ad.DDP_ID equals d.ID
into dj from d in dj.DefaultIfEmpty()
join dd in dbHR.DepartmentDetails
on new {
key1 = d.ID, key2 = true
} equals new {
key1 = dd.DDP_ID,
key2 =
(
DbFunctions.TruncateTime(dd.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
&& DbFunctions.TruncateTime(dd.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
)
} into ddj from dd in ddj.DefaultIfEmpty()
select new HREmployeeRecord
{
i=i,
p= p,
s=s,
e=e,
a=a,
ad=ad,
j=j,
jd=jd,
d=d,
dd=dd
}
The resulting SQL generated by EF has left joins on most of the tables, but 3 of them keep reverting to inner joins:
FROM "DB"."DB_HR_IDENTITIES" "Extent1"
LEFT OUTER JOIN "DB"."DB_HR_PERSONALS" "Extent2" ON ("Extent1"."ID" = "Extent2"."EID_ID") AND [...datetime comparison removed for brevity...]
LEFT OUTER JOIN "DB"."DB_CM_STATE_PROVINCES" "Extent3" ON "Extent2"."DSP_ID_ADDRESS" = "Extent3"."ID"
LEFT OUTER JOIN "DB"."DB_HR_EMPLOYMENTS" "Extent4" ON ("Extent1"."ID" = "Extent4"."EID_ID") AND [...datetime comparison removed for brevity...]
LEFT OUTER JOIN "DB"."DB_HR_ASSIGNMENTS" "Extent5" ON ("Extent4"."ID" = "Extent5"."EEM_ID") AND [...datetime comparison removed for brevity...]
INNER JOIN "DB"."DB_HR_ASSIGNMENT_DETAILS" "Extent6" ON ("Extent5"."ID" = "Extent6"."EAS_ID") AND ((TRUNC("Extent6"."EXPIRY")) >= (TRUNC(LOCALTIMESTAMP))) AND ((TRUNC("Extent6"."EFFECTIVE")) <= (TRUNC(LOCALTIMESTAMP)))
INNER JOIN "DB"."DB_CM_JOBS" "Extent7" ON "Extent6"."DJB_ID" = "Extent7"."ID"
LEFT OUTER JOIN "DB"."DB_CM_JOB_DETAILS" "Extent8" ON ("Extent7"."ID" = "Extent8"."DJB_ID") AND [...datetime comparison removed for brevity...]
INNER JOIN "DB"."DB_CM_DEPARTMENTS" "Extent9" ON "Extent6"."DDP_ID" = "Extent9"."ID"
LEFT OUTER JOIN "DB"."DB_CM_DEPARTMENT_DETAILS" "Extent10" ON ("Extent9"."ID" = "Extent10"."DDP_ID") AND [...datetime comparison removed for brevity...]
I've tried replacing some of the joins with the following, but unfortunately it results in identical output:
from ad in dbHR.AssignmentDetails.Where(x=>
a.ID == x.EAS_ID &&
(
DbFunctions.TruncateTime(x.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
&& DbFunctions.TruncateTime(x.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
)
).DefaultIfEmpty()
I'm beginning to pull my hair out.
EF seems to use INNER JOIN for including a required and LEFT OUTER JOIN for including an optional navigation property.
While that makes sense, I don't think it applies to my AssignmentDetails table, as it's the Many (FK) side of a One (Assignments) to Many (AssignmentDetails) relationship, where there could possibly not be an AssignmentDetails record.
It may be true, however, for the Departments and Jobs tables, since they're the primary key table referenced by a foreign key - there should always be a record. However, this still breaks the results because if in the join the FK is on a table that was NULL due to a left join, then the entire row is then removed due to the INNER join.
Any assistance would be greatly appreciated!
My mistake. I was wrapping this query in a function called GetEmployees() which returned an iQueryable list, and was filtering it:
results = HRFunctions.GetEmployees().Where(x => x.ad != null);
Because there was a WHERE clause added that assumed AD should not be null, EntityFramework intelligently altered the LEFT JOIN to an INNER JOIN.
After removing the .Where(), AssignmentDetails is now a Left Join.