OrmLite Complex Join Example

main.cs

using System;
using System.Collections.Generic;
using ServiceStack;
using ServiceStack.Logging;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using ServiceStack.DataAnnotations;

LogManager.LogFactory = new ConsoleLogFactory();

var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.Open(); // Open ADO.NET DB Connection
OrmLiteConfig.DialectProvider.NamingStrategy = new LowercaseUnderscoreNamingStrategy();

public class Customer
{
    [AutoIncrement]
    public long Id { get; set; }
    public string Name { get; set; }
}

public class Purchase
{
    [AutoIncrement]
    public long Id { get; set; }
    public long CustomerId { get; set; }
    public DateTime Date { get; set; }
}

db.CreateTable<Customer>();
db.CreateTable<Purchase>();

var customer = new Customer {
    Name = "Customer Name",
};
db.Save(customer);

db.SaveAll(new[] {
    new Purchase { CustomerId = customer.Id, Date = new DateTime(2001,01,01) },
    new Purchase { CustomerId = customer.Id, Date = new DateTime(2002,01,01) },
});

var results = db.Select<Tuple<Customer,Purchase>>(@"SELECT c.*, 0 EOT, p1.*
    FROM customer c
    JOIN purchase p1 ON (c.id = p1.customer_id)
    LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
        (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
    WHERE p2.id IS NULL;");

results.PrintDump();

packages.config

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="ServiceStack.Text" version="4.5.0" targetFramework="net45" />
  <package id="ServiceStack.Interfaces" version="4.5.0" targetFramework="net45" />
  <package id="ServiceStack.Common" version="4.5.0" targetFramework="net45" />
  <package id="ServiceStack.OrmLite" version="4.5.0" targetFramework="net45" />
  <package id="ServiceStack.OrmLite.Sqlite.Mono" version="4.5.0" targetFramework="net45" />
</packages>