Extend AutoQuery with JOIN on non-primary keys

Hi,

Since I already know that AutoQuery only supports join on primary keys (servicestack - Can OrmLite specify foreign key to an attribute other than the primary key - Stack Overflow), I’m trying to create a custom implementation.

Tables Department and Employee both have simple primary keys called “MDK”, but because of legacy issues, I need to join on other columns, which I’ve specified in the Join here:

Want the output such as this:

public class AnnotatedDepartment : Department
{
    public string ManagerFullName { get; set; }
    public string ManagerEmail { get; set; }

    public Employee Manager { get; set; }
}

Where Department is 1:1 with a table as usual. Same goes for Employee, although the property is named Manager.

Having ManagerFullName as a string when it’s included inside Employee is just for test purposes.

The AutoQuery custom implementation is this:

using var db = AutoQuery.GetDb(request, base.Request);  // AutoQuery is injected
var q = AutoQuery.CreateQuery(request, base.Request);

q = q.LeftJoin<Employee>((d, e) => e.EmpNo == d.MgrEmpNo)
            .Select<Department,Employee>((d,e) => new {   
                d.DepName,
                d.DepNo,
                d.DepParentNo, 
                d.DepPath,
// do I really have to spell out each and every property from the base class Department?
// is there a smarter way
                ManagerFullName =e.FullName,
                ManagerEmail = e.EMail,
                Manager = e // see if I could get the whole object, but it was empty
            });

        var results = AutoQuery.Execute(request, q); // output is a class AnnotatedDepartment, which has the extra columns

Without too much insight into how the AutoQuery.Execute works, what I can see is that the simple string attributes do get values, as well as every attribute I specify, but the Manager which is an object is blank.

I guess I’m just looking for some best practice when it comes to how to deal with AutoQuery and annotating classes with extra info. I’ve seen the IJoin stuff, as well as some [ReferenceField] stuff, both which seems to be made for the purpose, but I’m always denied these pleasures not using the PK as the FKs.

I’ve got another example w. same tables as well:

class Employee {
[AutoIncrement]
  public int MDK {get;set;}  

  public int EmpNo { get; set; }  // legacy key
  public string FullName { get; set; }
  public List<Department> ManagesDepartments {get;set;} // Department has attribute MgrEmpNo -> EmpNo
}

public class AnnotatedEmployee : Employee
{
    public List<Department> ManagesDepartments { get; set; }// only in use for Manager query
}

How would I go about populating ManagesDepartments?

W/O AutoQuery what I’d do is db.SelectMulti(), then using a loop and putting things in the correct spots.

Yeah you’re not going to get AutoQuery’s Auto Behavior unless it’s able to infer the FK relationship.

FYI the implementation of AutoQuery.Execute is effectively:

var include = q.OnlyFields;
var response = new QueryResponse<Into>
{
    Offset = q.Offset.GetValueOrDefault(0),
    Results = db.LoadSelect<Into, From>(q, include:include),
};

Which is just executing the query with LoadSelect and populating the QueryResponse<T> response with the results which you can do yourself instead.

As there’s no built-in support for populating non-related reference properties you may as well populate the QueryResponse<T> yourself using .SelectMulti<Department,Employee>() then populating the QueryResponse<T> Results with the dataset from the 2 tables.

An alternative approach would be to run a separate query to fetch the manager’s in a dictionary and stitch it together that way, which is the approach Merge() extension method uses to stitch disconnected resultsets together.

Note selecting an entire object is an alias for selecting all fields from that object.

1 Like

Hi again,
Some observations, and a question in the end.

Note selecting an entire object is an alias for selecting all fields from that object.

Great tip! I can now easily extend without typing out all the columns:

q = q.LeftJoin<Employee>((d, e) => e.EmpNo == d.MgrEmpNo)
            .Select<Department, Employee>((d, e) => new {
                d,        // <--- all columns from Department (shorthand) 
                ManagerFullName = e.FullName, 
                ManagerEmail = e.EMail,       
            });

        var results = AutoQuery.Execute(request, q);

Changes from standard AutoQuery:

  • OnlyFields seems broken, perhaps because the Select? Don’t know about OrmLite but in SQL this would be solved with an outer select picking the columns. Not a big deal, unused feature for my part.

  • Locode icon disappeared from overriden service in API Explorer. However switching to Locode I can find the service but without the extra columns. Perhaps better to keep two services:

    • One plain service QueryDepartments (no override, just the DTO), 1:1 to the table
    • Another QueryDepartmentsPlus with annotations (the one shown here) – would be nice to show in Locode as well, with the extra columns

Not totally satisfactory, as it would’ve been nice to Locode query with extra cols.
Question: Any way to make the Locode works with an overriden AutoQuery service (for the query part, not the updating)?

One more thing:

The approach above, with the Select and anonymous type works fine for adding simple fields (string, int), but I did not get adding objects to work, like adding the whole Employee (Manager) inside the Department.
To do that, I had to SelectMulti and merge things together myself. This is fine, except that I suspect that all those nice built-in AutoQuery filters are lost.

Right, the custom OnlyFields select list is being overridden with your custom select.

Not sure why that is, if it retains the same AutoQuery API definition it shouldn’t matter if it has a custom implementation or not. You can try inspecting your App’s /metadata/app.json which powers Locode’s UI to see if anything has changed in its /api/operations/<RequestDto> definition.

The purpose of the .Select() SqlExpression API is only to construct the SQL SELECT expression to query, it’s decoupled from how the results are mapped, i.e. it’s not used for mapping or projection. The API you choose to execute the db.From<T> SqlExpression with determines how results are mapped, e.g. you’d typically map the returned results to a typed POCO with db.Select<T>(q), but you could also map it to a dynamic result set.

but I did not get adding objects to work

As mentioned previously the behavior of selecting an entire table is to expand the select expression to include all columns of that table. It shouldn’t be used for anything but specifying what should be included in the SQL SELECT expression.

This is fine, except that I suspect that all those nice built-in AutoQuery filters are lost.

Your filters shouldn’t be lost. Calling AutoQuery.CreateQuery is what creates a populated SqlExpression<T> from the AutoQuery API Request:

var q = AutoQuery.CreateQuery(request, base.Request);

From there you can override the SqlExpression like you’re doing in your custom select, but the existing filters should be preserved if you don’t override or clear them.

1 Like

Here’s a diff of the meta data. After I took the diff I have changed GET to ANY to see if there was any difference. It’s just the boat icon that’s missing.

This is not a big problem for me. So more FYI.

If I use the boat icon from anywhere else, both services appear in the list on the left and can be called, however without the extra columns from the AnnotatedDepartment class.

Here’s the DTOs:

    [Route("/departments", "GET")]
    [Route("/departments/{DepNo}", "GET")]
    [ValidateRequest("IsAuthenticated")]
    public class QueryDepartments
        : QueryDb<Department>, IReturn<QueryResponse<Department>>, IGet
    {
        public int? DepNo { get; set; }
        public bool? Active { get; set; }
    }
    

    [ValidateRequest("IsAuthenticated")]
    public class QueryDepartmentsPlus
    : QueryDb<Department, AnnotatedDepartment>, IReturn<QueryResponse<AnnotatedDepartment>>, IGet
    {
        public int? DepNo { get; set; }
        public bool? Active { get; set; }
    }

Only one of the AutoQuery APIs per DataModel will be selected to be used by Locode. You can try changing the one you don’t want to be used by Locode to change its QueryDb<> base type to just use QueryBase.

Note: You don’t need to use IReturn<T> when inheriting from QueryDb<T> since it already implements it.