Inisght.Database Auto Repository and Custom Column Mapping

Question

I am trying to implement Insight.Database in a project, and have run into a brick wall trying to utilize the automatic interface implementation AND mapping object properties to odd column names in the database.

I have the following structure...

class Employee 
{
    string EmployeeCode {get; set;}
    string Name {get; set;}
}

class Vacation 
{
    int VacationId {get; set;}
    DateTime VacationDate {get; set;}
    string EmployeeCode {get; set;}
    string ApprovedByEmployeeCode {get; set;}
    Employee Employee {get; set;}
    Employee ApprovedByEmployee {get; set;}
}

My database looks like....

Table: Employees (EmployeeCode, [Name])

Table: Vacations (VacationId, VacationDate, EmployeeCode, ApprovedByEmployeeCode)

View: VacationsView (so I don't have to keep writing [and changing] the same SELECT over and over)

SELECT v.VacationId, v.VacationDate, v.EmployeeCode, v.ApprovedByEmployeeCode, e1.EmployeeCode AS CreatedByEmployeeCode, e1.[Name] AS CreatedByName, e2.EmployeeCode AS ApprovingEmployeeCode, e2.[Name] AS ApprovingName
FROM Vacations v 
INNER JOIN Employees e1 ON v.EmployeeCode = e1.EmployeeCode
INNER JOIN Employees e2 ON v.ApprovedByEmployeeCode = e2.EmployeeCode

Stored Procedure: GetAllVacations

SELECT * FROM VacationsView

Finally, with Insight.Database, I am trying to have an interface that will autopopulate my objects and tell it how to use the different column names from my stored procedure for the "employee" properties.

public interface IMyRepository
{
    IList<Vacation> GetAllVacations();
}

....

var repo = conn.As<IMyRepository>();
return repo.GetAllVacations();

This works (as in doesn't error) and all the properties of my vacation are correctly mapped, but my two "employee" properties are null (as expected because the column names don't line up to the property names of an employee object). What I can't figure out is how to tell Insight "Use CreatedBy.." fields to build the "Employee" property and "Approving..." fields to build the "ApprovedByEmployee" property.

I have been able to accomplish it using OneToOne with a callback and columnOverride and then use a standard Query(). I.E..

var vacationStructure =
            new OneToOne<Vacation, Employee, Employee>(
    callback: (vacation, createdBy, approvedBy) =>
    {
            vacation.Employee = createdBy;
            vacation.ApprovedByEmployee = approvedBy;
    }, columnOverride: new ColumnOverride[]
    {
            new ColumnOverride<EmployeeModel>("CreatedByEmployeeCode", "EmployeeCode"),
            new ColumnOverride<EmployeeModel>("CreatedByName", "Name"),
            new ColumnOverride<EmployeeModel>("ApprovingEmployeeCode", "EmployeeCode"),
            new ColumnOverride<EmployeeModel>("ApprovingName", "Name")
    });

....


var results = await conn.QueryAsync("GetAllVacations", new {employeeCode}, Query.Returns(_vacationStructure));

However, I'm really trying to utilize the auto interface capabilities of Insight.

Is what I'm trying to do possible?

Answer 1

Assembling repeated child objects isn't something that's currently done automatically, and the interface implementation doesn't give you the right hooks to override the behavior.

Some options:

A. Change the shape of your result set to return the employees as a list with properties.

B. If the classes aren't sealed, derive from Employee so Insight can differentiate between the classes:

public class AssigningEmployee : Employee { public string AssigningName { get { return Name; } set { Name = Value; } } ... }

These solutions are all meh. The whole point of Insight.Database is to just work without a lot of extra work. So...

I opened a github issue to track this:

https://github.com/jonwagner/Insight.Database/issues/384