servicestack - 自动查询:通过中间表联接表并定义要联接的FK

我们最近开始使用ServiceStack自动查询。这是一个不错的功能，我们非常喜欢。我们有一个这样的表结构(缩小版本以使其易于阅读):

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是快捷方式，并且是为其他方案设计的。

所以当我像这样定义自动查询时

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 */

最佳答案

如果您想要其他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);
    }
}

注意:从v4.0.56起，QueryBase<T>是deprecated and renamed to QueryDb。