OrmLite Many to Many Post Example

main.cs

using System;
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 Post
{
    [AutoIncrement]
    public long Id { get; set; }
    [Default(OrmLiteVariables.SystemUtc)]
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
}

public class Tag
{
    public long Id { get; set; }
    public string Name { get; set; }
}

public class PostTag
{
    [AutoIncrement]
    public long Id { get; set; }
    [References(typeof(Post))]
    public long PostId { get; set; }
    [References(typeof(Tag))]
    public long TagId { get; set; }
}

db.CreateTable<Post>();
db.CreateTable<Tag>();
db.CreateTable<PostTag>();

var post1Id = db.Insert(new Post { CreatedBy = "gistlyn", Title = "Post 1", Body = "Body 1" }, selectIdentity:true);
var post2Id = db.Insert(new Post { CreatedBy = "gistlyn", Title = "Post 2", Body = "Body 2" }, selectIdentity:true);
db.Insert(new Tag { Id = 1, Name = "A" }, new Tag { Id = 2, Name = "B" });
db.Insert(new PostTag { PostId = post1Id, TagId = 1 }, new PostTag { PostId = post1Id, TagId = 2 });
db.Insert(new PostTag { PostId = post2Id, TagId = 1 });

var postsWithTagA = db.Select(db.From<Post>()
                                .Join<PostTag>()
                                .Join<PostTag,Tag>()
                                .Where<Tag>(x => x.Name == "A"));
postsWithTagA.PrintDump();

var postsWithTagB = db.Select(db.From<Post>()
                                .Join<PostTag>()
                                .Join<PostTag,Tag>()
                                .Where<Tag>(x => x.Name == "B"));
postsWithTagB.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>