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.
