OrmLite 1:Many projection demo

main.cs

using System;
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 Book
{
    [AutoIncrement]
    public int Id {get; set;}
    public string Title {get; set;}
    [Reference]
    public List<BookAuthor> BookAuthors {get; set;}
}

[UniqueConstraint(nameof(BookId), nameof(AuthorId))]
public class BookAuthor
{
    [AutoIncrement]
    public int Id {get; set;}

    [ForeignKey(typeof(Book))]
    public int BookId {get; set;}
    
    [ForeignKey(typeof(Author))]
    public int AuthorId {get; set;}
}

public class Author
{
    [AutoIncrement]
    public int Id {get; set;}
    public string Name {get; set;}
}

public class BookDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<Author> Authors { get; set; }
}

db.CreateTable<Book>();
db.CreateTable<Author>();
db.CreateTable<BookAuthor>();

var book1Id = db.Insert(new Book { Title = "Book 1" }, selectIdentity:true);
var book2Id = db.Insert(new Book { Title = "Book 2" }, selectIdentity:true);
var book3Id = db.Insert(new Book { Title = "Book 3" }, selectIdentity:true);

var authorAId = db.Insert(new Author { Name = "Author A" }, selectIdentity:true);
var authorBId = db.Insert(new Author { Name = "Author B" }, selectIdentity:true);

db.Insert(new BookAuthor { BookId = 1, AuthorId = 1 });
db.Insert(new BookAuthor { BookId = 1, AuthorId = 2 });
db.Insert(new BookAuthor { BookId = 2, AuthorId = 2 });
db.Insert(new BookAuthor { BookId = 3, AuthorId = 2 });

var q = db.From<Book>()
    .Join<BookAuthor>()
    .Join<BookAuthor,Author>()
    .Select<Book,Author>((b,a) => new { b, a });
var results = db.SelectMulti<Book,Author>(q);

var booksMap = new Dictionary<int,BookDto>();
results.Each(t => {
    if (!booksMap.TryGetValue(t.Item1.Id, out var dto))
        booksMap[t.Item1.Id] = dto = t.Item1.ConvertTo<BookDto>();        
    if (dto.Authors == null) 
        dto.Authors = new List<Author>();
    dto.Authors.Add(t.Item2);
});

var dtos = booksMap.Values;
dtos.PrintDump();

packages.config

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