ServiceStack / ORM Lite - Foreign Key Relationships

Question

I have the following POCO:

[Alias("Posts")]
public class Post : IReturn<Post>
{
    [AutoIncrement]
    [PrimaryKey]
    public int PostId { get; set; }
    public DateTime CreatedDate { get; set; }
    [StringLength(50)]
    public string CreatedBy { get; set; }
    [StringLength(75)]
    public string Title { get; set; }
    public string Body { get; set; }
    public int UpVote { get; set; }
    public int DownVote { get; set; }
    public bool IsPublished { get; set; }

    public List<Comment> Comments { get; set; }
    public List<Tag> Tags { get; set; }
}

It has a FK on my Comment and Tag entities. So I'd like to return those in my response from my service, but it says 'Invalid Column name 'Comments'' and 'Invalid Column name 'Tags'' . How do I see which Comments and Tags are attached to my Post, with ORM Lite? In EF I would simply use Include to lazy load my related table information, whats the equivalent?

Edit

In response to the answers, I've done this:

public class PostFull
{
    public Post Post { get; set; }
    public List<Comment> Comments { get; set; }
    public List<Tag> Tags { get; set; }
}

Then in my service, I return this, my entity PostTag is an intersection entity as my Post and Tag entities are a M:M relationship:

var posts = Db.Select<Post>().ToList();
var fullPosts = new List<PostFull>();

posts.ForEach(delegate(Post post)
{
    var postTags = Db.Select<PostTag>(x => x.Where(y => y.PostId == 
    post.PostId)).ToList();

    fullPosts.Add(new PostFull()
    {
        Post = post,
        Tags = Db.Select<Tag>(x => x.Where(y => postTags.Select(z => 
                   z.TagId).Contains(y.TagId))).ToList(),
        Comments = Db.Select<Comment>(x => x.Where(y => y.PostId == 
                       post.PostId)).ToList()
    });
});

return fullPosts;

Not sure whether its a good design pattern or not?

Edit 2

Here are my entities:

[Alias("Tags")]
public class Tag
{
    [AutoIncrement]
    [PrimaryKey]
    public int TagId { get; set; }

    [StringLength(50)]
    public string Name { get; set; }
}

[Alias("Posts")]
public class Post
{
    [AutoIncrement]
    [PrimaryKey]
    public int PostId { get; set; }
    public DateTime CreatedDate { get; set; }
    [StringLength(50)]
    public string CreatedBy { get; set; }
    [StringLength(75)]
    public string Title { get; set; }
    public string Body { get; set; }
}

[Alias("PostTags")]
public class PostTag
{
    [AutoIncrement]
    [PrimaryKey]
    public int PostTagId { get; set; }

    [References(typeof(Post))]
    public int PostId { get; set; }

    [References(typeof(Tag))]
    public int TagId { get; set; }
}

Answer 1

Tables in OrmLite are strictly a 1:1 mapping with the underlying db tables.

This means all complex type properties are blobbed into a db text field with the property name, they're never used to auto-map to child relations as you're expecting to do here.

Here's an early answer that shows how you could map many to many relations with OrmLite.

Try to avoid N+1 queries, remember that every call to Db.x is a remote DB query so you should ideally try to avoid any Database calls in a loop.

Retrieving Posts by Many to Many Table query

You can use OrmLite's support for JOINs to construct a Typed query as you would in normal SQL to query by the Many to Many table and find all posts with the specified Tag:

Create and Populate Posts with Test Data

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 });

Create a SQL Expression Joining all related tables:

When following OrmLite's normal naming conventions above, OrmLite can infer the relationship between each table saving you from specifying the JOIN expression for each query, e.g:

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

Where this Query returns just the first Post for Tag B and both Posts for Tag A.

You can further explore this stand-alone example online by running it Live on Gistlyn.

Populating all Posts with Tags and Comments

If this is a small blog and you want to load all the posts with their related tags and comments e.g. in a home page or RSS feed you can load the entire dataset in memory with 4 queries using Linq2Objects to join them with something like:

//Only 4 DB calls to read all table data
var posts = Db.Select<Post>();
var postTags = Db.Select<PostTag>();
var tags = Db.Select<Tag>();
var comments = Db.Select<Comment>();

//using Linq2Objects to stitch the data together
var fullPosts = posts.ConvertAll(post =>
{
    var postTagIds = postTags
        .Where(x => x.PostId == post.PostId)
        .Select(x => x.PostTagId).ToList();

    return new PostFull {
        Post = post,
        Tags = tags.Where(x => postTagIds.Contains(x.TagId)).ToList(),
        Comments = comments.Where(x => x.PostId == post.PostId).ToList(),
    };
});