ServiceStack LoadSelect not properly <Into> references

Question

I have a case where I want to use ServiceStack.OrmLite LoadSelect to populate a POCO that is comprised of columns from multiple tables.

The basic background is that there are service requests (for a field tech to complete), and a separate assignment table. For part of the application, I want to combine ServiceRequest and ServiceRequestAssignment into a single ServiceRequestExtended class.

Here are the POCOs:

public class ServiceRequest
{
    [AutoIncrement]
    public int Id { get; set; }

    ... 

}

public class ServiceRequestAssignment
{
    [AutoIncrement]
    public int Id { get; set; }

    [References(typeof(User))]
    public int? UserId { get; set; }

    [Reference]
    public User User { get; set; }

    [References(typeof(ServiceRequest))]
    public int ServiceRequestId { get; set; }

    [Reference]
    public ServiceRequest ServiceRequest { get; set; }

    public double RelativeOrder { get; set; }

    [RowVersion]
    public ulong RowVersion { get; set; }
}

// Basically combines all fields of ServiceRequest & ServiceRequestAssignment
public class ServiceRequestExtended : ServiceRequest
{
    [References(typeof(User))]
    public int? UserId { get; set; }
    [Reference]
    public User User { get; set; }

    public double RelativeOrder { get; set; }
    public ulong RowVersion { get; set; }
}

Here is the (general) query being used:

var exp = Db.From<ServiceRequest>()
          .Join<ServiceRequestAssignment>()
          .Where<ServiceRequest, ServiceRequestAssignment>((q, r) => ...);

var results = Db.LoadSelect<ServiceRequestExtended, ServiceRequest>(exp);

When I do this, the query that LoadSelect generates to bring in the associated tables / POCOs is incorrect. It references ServiceRequestExtended instead of the proper child table (in this case ServiceRequestAssignment) in the subselect.

Here's the SQL:

SELECT "Id", "UserName", "TechnicianNumber", "FirstName", "LastName", "PIN",     "Active", "Salt"
FROM "Users" WHERE "Id" 
IN (SELECT "ServiceRequestExtended"."UserId" 
    FROM "ServiceRequest" 
    INNER JOIN "ServiceRequestAssignment"
    ON ("ServiceRequest"."Id" = "ServiceRequestAssignment"."ServiceRequestId")
    WHERE ...)

So - what am I doing wrong? Is there a bug in LoadSelect<Into, From>?

Answer 1

OrmLite Self References can only be on real tables as OrmLite needs to query the existing reference field Ids to populate the POCO references.