servicestack - 自动查询 : join tables via a middle table and define which FK to join on

Question

我们最近开始使用 ServiceStack AutoQuery。这是一个很好的功能，我们非常喜欢它。我们有一个这样的表结构(缩小版本以便于阅读):

Salary [Id (PK), ManagerId (FK)] 
Manager [Id (PK), DepartmentId (FK)] /* Manager is like Employee table even thought it's named Manager */
Department [Id (PK), ActiveManagerId (FK)] /* The FK ActiveManagerId is supposed to be a shortcut, it's Nullable.  */

所以理论上我们可以这样连接

Salary -> Manager via Salary.ManagerId = Manager.Id
Manager -> Department via Manager.DepartmentId = Department.Id
Department -> Manager via Department.ActiveManagerId = Manager.Id

但是在这种特定情况下，如果我们通过 Department.ActiveManagerId = Manager.Id 从 Department 连接到 Manager，将不会产生正确的结果，因为 Department.ActiveManagerId 是一种快捷方式，专为其他场景设计。

所以当我这样定义 AutoQuery 时

public class SalaryQuery : QueryBase<Salary, SalaryQueryResult>,
 ILeftJoin<Salary, Manager, Department>

下面的 SQL 是由 AutoQuery 生成的，从 ServiceStack AutoQuery 的角度来看是正确的。

select  s.Id
    ,   d.Id
from    Salary s
    left join
        Manager m
    on  s.ManagerId = m.Id
    left join
        Department d
    on  d.ActiveManagerId = m.Id /* should NOT use shortcut: Department -> Manager via Department.ActiveManagerId = Manager.Id */

但相反，我们希望能够生成如下所示的 SQL

select  s.Id
    ,   d.Id
from    Salary s
    left join
        Manager m
    on  s.ManagerId = m.id
    left join
        Department d
    on  d.Id = m.DepartmentId /* should use the desired FK: Manager -> Department via Manager.DepartmentId = Department.Id */

Answer 1

如果您想要不同的 JOIN 行为，您需要在 Custom AutoQuery implementation 中添加自定义 LEFT JOIN ，例如:

//AutoQuery DTO
public class SalaryQuery : QueryDb<Salary,SalaryQueryResult>, ILeftJoin<Salary,Manager>

//Implementation
public class MyServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    public object Any(SalaryQuery query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request)
            .LeftJoin<Manager, Department>((m, d) => d.Id == m.DepartmentId);

        return AutoQuery.Execute(query, q);
    } 
}

Note: From v4.0.56 QueryBase<T> has been deprecated and renamed to QueryDb.