c# - 如何在Sqlkata中形成多个Join Conditions sql查询

Question

我正在尝试通过使用 Sqlkata 来构建 Sql 查询。我能够为一个连接条件构建正确的 SQL 查询，但是对于 And 条件我遇到了问题

var empDeptQuery = new Query("employee");
empDeptQuery.Select("employee.Name", "dept.Deptname");
empDeptQuery.Join("dept", join => join.On("employee.deptid", "dept.deptid"));          

SqlResult empDeptSqlKataQuery = compiler.Compile(empDeptQuery);

最终查询 -empDeptSqlKataQuery.Sql 是

SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid])

我想像下面这样在 join 子句中添加更多条件。

SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid] And [employee].[empdeptname]=[dept].[departmentName])

我试过了，但没有达到我的预期

var empDeptQuery = new Query("employee");
empDeptQuery.Select("employee.Name", "dept.Deptname");
empDeptQuery.Join("dept", join => join.On("employee.deptid", "dept.deptid"));
empDeptQuery.Join(empDeptQuery, join => join.On("employee.empdeptname", "dept.departmentName"));

 SqlResult empDeptSqlKataQuery = compiler.Compile(empDeptQuery);

使用上述方法的最终 Sql 查询:

SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid])
INNER JOIN (SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid])) ON ([employee].[empdeptname] = [dept].[departmentName])

请告诉我如何解决这个问题

Answer 1

根据需要使用 On 或 WhereX 方法，例如

new Query("employee")
    .Select("employee.Name", "dept.Deptname")
    .Join("dept", 
        j => j.On("employee.deptid", "dept.deptid")
            .On("employee.countryid", "dept.countryid")            
    )

请注意 On 只是 WhereColumns 方法的别名，因此所有 WhereX 方法也适用于此上下文