ServiceStack / ORM Lite - External Relations
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 FK for my objects Comment
and Tag
. So I would like to return them in my answer from my service, but he says 'Invalid Column name 'Comments''
and 'Invalid Column name 'Tags''
. How do I find out what comments and tags are attached to my post with ORM Lite? In EF, I would just use Include to lazy load my linked data table, what is the equivalent?
Edit
In response to the answers, I did the following:
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 object, since my objects are Post
and Tag
are an 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 if this is 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; }
}
source to share
Tables in OrmLite are strictly 1: 1 with base db tables.
This means that all the complex type blobbed properties in the db textbox with the property name, they are never used to automatically bind to child relationships as you would expect to do here.
Here's an early answer that shows how you could map many, many relationships to OrmLite .
Try to avoid N + 1 queries , remember that every call Db.x
is a remote DB query, so you should ideally try to avoid any database calls in a loop.
Receiving messages across many tables
You can use OrmLite support for JOINs to build a typed query as you would in regular SQL for a query over a Many to Many table and Find all posts with this tag:
Create and Populate Messages with Test Datadb.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 an SQL expression. Concatenation of all related tables:
By following the usual OrmLite naming conventions above, OrmLite can infer the relationship between each table , saving you the trouble of specifying a JOIN expression for each query, for example:
var postsWithTagB = db.Select(db.From<Post>()
.Join<PostTag>()
.Join<PostTag,Tag>()
.Where<Tag>(x => x.Name == "B"));
postsWithTagB.PrintDump();
If this query returns only the first message for the tag B
and both entries for the tag A
.
You can further explore this standalone example online by running it Live on Gistlyn .
Filling all posts with tags and comments
If it's a small blog and you want to download all posts with the appropriate tags and comments, eg. on the 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(),
};
});
source to share
You don't need to include tags and comments in the Post object. In your case, the DTO and DB classes should be different. In the tag and comment classes, you must have a PostId property.
In the service, you have to request comments where PostId is equal to your post id and do the same for tags. The results should be added to your DTO post containing comment and tag lists.
source to share