Entity Framework双可空列左连接生成多余OR条件致结果异常

现有LINQ查询代码如下：

from reasonCodes in _context.ReasonCodes
join mapping in _context.MappedCodes on
new {
    ReasonCode = reasonCodes.Code, StateUid = filter.StateID, VendorUid = filter.VendorID, MappingType = filter.MappingTypeID
}
equals
new {
    ReasonCode = mapping.ValueToMap, StateUid = mapping.StateUid, VendorUid = mapping.VendorUid, MappingType = mapping.Type
}
into mappingInner
from mapping in mappingInner.DefaultIfEmpty()
join emp in _context.Employees on mapping.ModifiedBy equals emp.UseraccountUid into empInner
from emp in empInner.DefaultIfEmpty()
select new MappingSearchResult {
    ReasonCode = reasonCodes.Code,
    MappedReasonCode = mapping.MappedCode,
    DateModified = (mapping != null) ? mapping.ModifiedDate : null,
    ModifiedBy = (mapping != null) ? (emp != null ? emp.Emplname + ", " + emp.Empfname : null) : null
};

执行时与Employees表做左连接的环节出现异常：MappedCodes.ModifiedBy、Employees.UseraccountUid两个字段均为可空GUID类型，EF自动在连接逻辑中生成了多余SQL片段：

OR (
    ([MappedCodes].[ModifiedBy] IS NULL)
     AND ([Employees].[useraccount_uid] IS NULL)
)

该条件会导致查询返回数千行冗余数据，而业务预期是MappedCodes表无匹配数据时仅返回约15条ReasonCodes基础记录。需求为返回ReasonCodes表全部编码，关联匹配存在的MappedCodes、Employees对应数据，无匹配时仅填充ReasonCode字段即可。编写原生SQL可实现预期效果，但需要对应正确的LINQ写法。

C#语言默认语义中null == null返回true，EF翻译LINQ连接逻辑时会遵循这个语义，自动补充两个NULL值相等的判断；但数据库SQL默认语义中NULL = NULL返回UNKNOWN（不视为相等），这个语义差导致生成了不符合业务预期的多余OR条件，把所有两边连接字段都是NULL的无关记录也关联到了结果中。

修改可空字段的连接逻辑，显式指定仅当MappedCodes.ModifiedBy非空时才做值匹配，避免EF生成NULL相等的判断，推荐写法如下：

from reasonCodes in _context.ReasonCodes
join mapping in _context.MappedCodes on
new {
    ReasonCode = reasonCodes.Code, 
    StateUid = filter.StateID, 
    VendorUid = filter.VendorID, 
    MappingType = filter.MappingTypeID
}
equals
new {
    ReasonCode = mapping.ValueToMap, 
    StateUid = mapping.StateUid, 
    VendorUid = mapping.VendorUid, 
    MappingType = mapping.Type
}
into mappingInner
from mapping in mappingInner.DefaultIfEmpty()
// 修改员工表连接条件，显式标记仅ModifiedBy非空时才参与匹配
join emp in _context.Employees on 
new { Valid = true, Uid = mapping.ModifiedBy } 
equals 
new { Valid = mapping.ModifiedBy.HasValue, Uid = emp.UseraccountUid } 
into empInner
from emp in empInner.DefaultIfEmpty()
select new MappingSearchResult {
    ReasonCode = reasonCodes.Code,
    MappedReasonCode = mapping.MappedCode,
    DateModified = mapping != null ? mapping.ModifiedDate : null,
    ModifiedBy = mapping != null 
        ? (emp != null ? emp.Emplname + ", " + emp.Empfname : null) 
        : null
};

如果使用EF Core 5及以上版本，也可以直接将员工表关联逻辑改为带条件的DefaultIfEmpty写法，生成的SQL更简洁：

// 替换原代码中Employees表join的整段逻辑即可
from emp in _context.Employees
    .Where(e => mapping != null && mapping.ModifiedBy == e.UseraccountUid)
    .DefaultIfEmpty()

两种写法生成的SQL都会去掉多余的OR 双NULL匹配片段，返回结果符合预期：无匹配MappedCodes记录时，仅返回ReasonCodes的基础数据，不会关联出冗余的员工记录。

内容的提问来源于stack exchange，提问作者Cameron