Custom SQL with Custom Select Fields demo

main.cs

using System.Collections.Generic;
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 Custom1
{
    public int Id { get; set; }
    public string Field1 { get; set; }
    public string Field2 { get; set; }
}

public class Custom2
{
    public int Id { get; set; }
    public int Custom1Id { get; set; }
    public string Field3 { get; set; }
    public string Field4 { get; set; }
}

db.DropAndCreateTable<Custom1>();
db.DropAndCreateTable<Custom2>();

db.Insert(new Custom1 { Id = 1, Field1 = "f1", Field2 = "f2" });
db.Insert(new Custom2 { Id = 2, Field3 = "f3", Field4 = "f4", Custom1Id = 1 });

var q = db.From<Custom1>()
    .LeftJoin<Custom2>()
    .Select<Custom1, Custom2>((t1, t2) => new
    {
        t1,
        t2,
        StatusText = Sql.Custom(@"(CASE Custom1Id 
            WHEN 1 THEN 'Awaiting First Appointment' 
            WHEN 3 THEN 'In Treatment' 
            WHEN 7 THEN 'On Hold' 
            WHEN 8 THEN 'Discharged' 
            WHEN 12 THEN 'Closed - Awaiting Review' 
            ELSE 'Closed and Reviewed' 
        END)")
    });

var results = db.Select<Dictionary<string, object>>(q);

db.GetLastSql().Print();

results.PrintDump();

packages.config

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