Entity Framework forcing Inner Join when Left Join DefaultIfEmpty() Syntax used

c# entity-framework-6 join linq-to-entities oracle

Question

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.

I read in this answer:

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!

1
2
5/23/2017 11:44:37 AM

Popular Answer

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.

2
12/15/2015 11:20:58 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow