OrmLite Tour

data.cs

using System.Collections.Generic;
using System.Data;
using ServiceStack;
using ServiceStack.OrmLite;
using ServiceStack.DataAnnotations;

public class Artist
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    [Reference]
    public List<Track> Tracks { get; set; }
    public override string ToString() => Name;
}

public class Track
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public int ArtistId { get; set; }
    public string Album { get; set; }
    public int Year { get; set; }
    public override string ToString() => Name;
}

public static class Seed 
{
    public static IDbConnection CreateArtistAndTrackTablesWithData(IDbConnection db)
    {
        db.DropAndCreateTable<Artist>();
        db.DropAndCreateTable<Track>();
        Artists.Each(x => db.Save(x, references:true));
        return db;
    }

    public static Artist[] Artists = new [] {
        new Artist { 
            Id = 1, Name = "Faith No More", 
            Tracks = new List<Track> { 
                new Track { Name = "Everythings Ruined", Album = "Angel Dust", Year = 1992 },
                new Track { Name = "Ashes to Ashes", Album = "Album of the Year", Year = 1997 },
            }
        },
        new Artist { 
            Id = 2, Name = "Live", 
            Tracks = new List<Track> { 
                new Track { Name = "Lightning Crashes", Album = "Throwing Copper", Year = 1994 },
                new Track { Name = "Lakini's Juice", Album = "Secret Samadhi", Year = 1997 },
            }
        },
        new Artist { 
            Id = 3, Name = "Nirvana", 
            Tracks = new List<Track> { 
                new Track { Name = "Smells Like Teen Spirit", Album = "Nevermind", Year = 1991  },
                new Track { Name = "Heart-Shaped Box", Album = "In Utero", Year = 1993 },
            }
        },
        new Artist { 
            Id = 4, Name = "Pearl Jam", 
            Tracks = new List<Track> { 
                new Track { Name = "Alive", Album = "Ten", Year = 1991 },
                new Track { Name = "Daughter", Album = "Vs", Year = 1993 },
            }
        },
    };
}

main.cs

using System;
using System.Linq;
using System.Collections.Generic;
using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;

var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.OpenDbConnection();

//DROP and CREATE Artist and Track Tables from their POCO definition
db.DropAndCreateTable<Artist>();
db.DropAndCreateTable<Track>();

//INSERT each Artist, including their Track references
Seed.Artists.Each(x => db.Save(x, references:true));

//SELECT all artists including their Track references
var allArtists = db.SqlList<dynamic>("Select * from Artist");
allArtists.PrintDump();

//SELECT all artists including their Track references
allArtists = db.SqlList<dynamic>("SELECT * FROM Artist");
allArtists.PrintDump();

MyApp.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
    <NoWarn>1591</NoWarn>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="ServiceStack.OrmLite.Sqlite" Version="5.*" />
  </ItemGroup>

</Project>