OrmLite Self Join

main.cs

using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using ServiceStack.DataAnnotations;

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

public class User
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }    
	[References(typeof(User))]
    public int SupervisorId { get; set; }
}
db.CreateTable<User>();
var super = new User {
    Id = 1,
    Name = "Sally Super",
};
db.Save(super);
var foreman = new User {
    Id = 2,
    Name = "Joe Foreman",
    SupervisorId = super.Id
};
db.Save(foreman);

public class TimeSheet
{
    [AutoIncrement]
    public int Id { get; set; }       
	[References(typeof(User))]
    public int ForemanId { get; set; }
}
db.CreateTable<TimeSheet>();
var ts1 = new TimeSheet {
    ForemanId = foreman.Id
};
db.Save(ts1);

/*
var savedSuper = db.SingleById<User>(super.Id);
"Saved super: {0}".Print(savedSuper.Dump());
var savedForeman = db.SingleById<User>(foreman.Id);
"Saved foreman: {0}".Print(savedForeman.Dump());

var savedts = db.SingleById<TimeSheet>(ts1.Id);
"Saved timesheet: {0}".Print(savedts.Dump());


var query = db.From<TimeSheet>()
.Join<User>()
    .Join<User, User>();
*/

var query = db.From<User>()
    .Join<User>((foreman, super) => foreman.SupervisorId == super.Id)
    .Where(u => u.Id == 2);
var results = db.Select(query);
"Results: {0}".Print(results.Dump());

packages.config

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