c# - Entity Framework 在左连接时强制内部连接使用 DefaultIfEmpty() 语法

Question

我正在尝试使用 .DefaultIfEmpty() 语法使用具有大量左连接的 Oracle EF 框架(Oracle.ManagedDataAccess.EntityFramework nuget 包，版本 12.1.2400)创建一个相当复杂的查询:

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
}

EF 生成的结果 SQL 在大多数表上留下了连接，但其中 3 个继续恢复为内部连接:

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...]

我已经尝试用以下内容替换一些连接，但不幸的是它会导致相同的输出:

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()

我开始拔头发了。

我read in this answer :

EF seems to use INNER JOIN for including a required and LEFT OUTER JOIN for including an optional navigation property.

虽然这是有道理的，但我认为它不适用于我的 AssignmentDetails 表，因为它是一个(分配)对多(AssignmentDetails)关系的多(FK)端，其中可能没有 AssignmentDetails 记录.

然而，对于 Departments 和 Jobs 表来说可能是正确的，因为它们是由外键引用的主键表 - 应该始终有一条记录。但是，这仍然会破坏结果，因为如果在连接中 FK 位于由于左连接而为 NULL 的表上，则由于 INNER 连接，整行将被删除。

如有任何帮助，我们将不胜感激!

Answer 1

我的错误。我将此查询包装在一个名为 GetEmployees() 的函数中，该函数返回一个 iQueryable 列表，并对其进行过滤:

results = HRFunctions.GetEmployees().Where(x => x.ad != null);

因为添加了一个假设 AD 不应该为 null 的 WHERE 子句，EntityFramework 智能地将 LEFT JOIN 更改为 INNER JOIN。

删除 .Where() 后，AssignmentDetails 现在是 Left Join。