How do I join 2 tables in ServiceStack OrmLite and select both classes?

Question

I'd like to do a simple SQL join in ServiceStack OrmLite and get both tables as the corresponding .NET objects.

In LINQ-to-Entities it would be something like this:

Claim.Join(Policy, c => c.PolicyId, p => p.Id, (c, p) => new { Claim = c, Policy = p })

This would give me an anonymous type with Claim and Policy properties.

I've looked at the OrmLite Advanced Join Example, but that only selects some of the properties of each type into a 3rd type of object (FullCustomerInfo). I don't want to repeat all my properties in another object, I just want to use the existing objects.

In reality, the query can be much more complex, e.g.

Claim.Join(Policy, c => c.PolicyId, p => p.Id, (c, p) => new { Claim = c, Policy = p })
    .Where(o => o.Policy.Something > o.Claim.Something)
    .Select(o => o.Claim.SomethingElse)

... etc., but even if OrmLite could just do the join in SQL and I had to do the rest in memory it would be a good start.

Answer 1

This wasn't available in OrmLite previously but as it's a nice feature to have I've just added support for SelectMulti<T,T2,..> and SelectMultiAsync in this commit which now lets you read up to 7 of your joined tables from a single query.

So to use OrmLite's Advanced Join Example you can construct a typed Join Query with:

var q = db.From<Customer>()
    .Join<Customer, CustomerAddress>()
    .Join<Customer, Order>();

Then use the SelectMulti APIs to populate the tables you're interested in, e.g:

var results = db.SelectMulti<Customer, CustomerAddress, Order>(q);

Which will return a List<Tuple<T,T2,T3>> giving you typed access to your populated POCOs from tables in your joined query.

If preferred, there's also an async version:

var results = await db.SelectMultiAsync<Customer, CustomerAddress, Order>(q);

The new SelectMulti APIs are available from v4.0.57 that's now available on MyGet.

Create Typed Queries in OrmLite and Execute them in Dapper

An alternative is to use a combination of OrmLite to create the typed query using its built-in Reference Conventions and then use OrmLite's embedded version of Dapper to Query Multiple result-sets into your existing POCO Types.

To start with create your Typed Query Expression and have it return all fields from all tables with:

var q = db.From<Customer>()
    .Join<Customer, CustomerAddress>()
    .Join<Customer, Order>()
    .Select("*");

Then pass the generated SQL from the above typed SQL Expression into Dapper's Query Multiple feature to read the results into a List of Tuples of the different joined tables:

using (var multi = db.QueryMultiple(q.ToSelectStatement()))
{
    var results = multi.Read<Customer, CustomerAddress, Order, 
        Tuple<Customer,CustomerAddress,Order>>(Tuple.Create).ToList();

    foreach (var tuple in results)
    {
        "Customer:".Print();
        tuple.Item1.PrintDump();

        "Customer Address:".Print();
        tuple.Item2.PrintDump();

        "Order:".Print();
        tuple.Item3.PrintDump();
    }
}

Which prints out something like:

Customer:
{
    Id: 1,
    Name: Customer 1
}
Customer Address:
{
    Id: 1,
    CustomerId: 1,
    AddressLine1: 1 Australia Street,
}
Order:
{
    Id: 1,
    CustomerId: 1,
    LineItem: Line 1,
    Qty: 1,
    Cost: 1.99
}    
Customer:
{
    Id: 1,
    Name: Customer 1
}
Customer Address:
{
    Id: 1,
    CustomerId: 1,
    AddressLine1: 1 Australia Street,
}
Order:
{
    Id: 2,
    CustomerId: 1,
    LineItem: Line 2,
    Qty: 2,
    Cost: 2.99
}