EF Core中多字段Join结合Coalesce的SQL转LINQ报错求助

原SQL语句 #

SELECT DISTINCT
    t1.*
FROM 
    table1 t1
INNER JOIN
    table2 t2
ON
    t1.SiteId = COALESCE(t2.SiteId, t1.SiteId)
AND
    t1.DepartmentId = COALESCE(t2.DeptID, t1.DepartmentId)
WHERE
    (t1.Id = @Id) AND (t2.UserID = @USERID) AND (CASE t2.IsRestricted WHEN 1 THEN t1.CreatedBy ELSE @USERID END = t2.UserID)

尝试的LINQ代码（报错） #

var query2 = 
    from t1 in _dbContext.table1
    join t2 in _dbContext.table2
        on new { t1.SiteId, t1.DepartmentId } 
        equals new { SiteId = t2.SiteId ?? t1.SiteId, DeptID = t2.DeptId ?? t1.DepartmentId }
    where t1.Id == request.Id && t2.UserID == request.userId 
        && (t2.IsRestricted == 1 ? t1.CreatedBy == t2.UserID : t2.UserID == request.userId)
    select t1;

报错信息 #

't1' 在equals右侧不在作用域内。请考虑交换equals两侧的表达式。

解决方法 #

错误原因 #

LINQ的join语法有严格作用域限制：equals右侧的表达式仅能引用join的右表（此处为t2），无法直接引用左表（t1），因此原写法会触发作用域错误。

等价的LINQ写法 #

将原SQL中JOIN ON的条件转换为WHERE子句的逻辑判断，即可保留原业务逻辑同时规避语法限制：

var query2 = 
    from t1 in _dbContext.table1
    from t2 in _dbContext.table2
    where t1.Id == request.Id 
        && t2.UserID == request.userId
        // 等价原SQL JOIN ON条件：t1.SiteId = COALESCE(t2.SiteId, t1.SiteId)
        && (t2.SiteId == null || t1.SiteId == t2.SiteId)
        // 等价原SQL JOIN ON条件：t1.DepartmentId = COALESCE(t2.DeptID, t1.DepartmentId)
        && (t2.DeptId == null || t1.DepartmentId == t2.DeptId)
        // 等价原SQL CASE条件
        && (t2.IsRestricted == 1 ? t1.CreatedBy == t2.UserID : t2.UserID == request.userId)
    select t1;

// 对应原SQL中的SELECT DISTINCT
var result = query2.Distinct().ToList();

逻辑说明 #

原SQL的COALESCE(t2.SiteId, t1.SiteId)逻辑为：当t2.SiteId为null时取t1.SiteId，因此t1.SiteId = COALESCE(...)可拆解为要么t2.SiteId等于t1.SiteId，要么t2.SiteId为null，与LINQ中的(t2.SiteId == null || t1.SiteId == t2.SiteId)完全等价。

若偏好使用join语法，也可通过join ... into结合SelectMany实现，但上述写法更简洁直观，且EF Core能将其正确转换为等价SQL查询。

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