ServiceStack JOIN 3 tables

Question

it is possible to join more than 2 tables with ServiceStack? I tried something like that but I need to link evrything:

// Join PatientDetails and BedDetails
SqlExpression<PatientDetails.PatientDetails> q = _dbConnection.From<PatientDetails.PatientDetails>();
q.Join<PatientDetails.PatientDetails, BedDetails.BedDetails>((patient, bed) => patient.ByBedId == bed.BedDetailsId);
List<PatientDetails.PatientDetails> PatientBedJOIN = _dbConnection.Select(q);

// Join PatientSession and PatientDetails
SqlExpression<PatientSession> q1 = _dbConnection.From<PatientSession>();
q1.Join<PatientSession, PatientDetails.PatientDetails>((session, patientd) => session.ByPatientId == patientd.PatientDetailsId);
List<PatientSession> SessionPatientJOIN = _dbConnection.Select(q1);

I'm using OrmLite and Sqlite, Thanks

Answer 1

OrmLite's Advanced Join Example in shows how you can just keep adding joins to the same query e.g:

List<FullCustomerInfo> rows = db.Select<FullCustomerInfo>(  // Map results to FullCustomerInfo POCO
  db.From<Customer>()                                       // Create typed Customer SqlExpression
    .LeftJoin<CustomerAddress>()                            // Implicit left join with base table
    .Join<Customer, Order>((c,o) => c.Id == o.CustomerId)   // Explicit join and condition
    .Where(c => c.Name == "Customer 1")                     // Implicit condition on base table
    .And<Order>(o => o.Cost < 2)                            // Explicit condition on joined Table
    .Or<Customer,Order>((c,o) => c.Name == o.LineItem));    // Explicit condition with joined Tables

Which in your example would look like:

var q = _dbConnection.From<PatientDetails.PatientDetails>()
    .Join<PatientDetails.PatientDetails, BedDetails.BedDetails>(
        (patient, bed) => patient.ByBedId == bed.BedDetailsId)
    .Join<PatientDetails.PatientDetails, PatientSession>(
        (patientd, session) => session.ByPatientId == patientd.PatientDetailsId);

var SessionPatientJOIN = _dbConnection.Select(q);