How to select one to many relationship & one to one together using dapper

Question

I have following classes and db schema. I am trying to query this data from database using dapper that would hydrate the full object graph. I looked at various SO question and the test but couldn't really figure out how to do this.

DB Schema

Author
  -AuthorId
  -Name

Post
  -PostId
  -Content
  -AuthorId

Comment
  -PostId
  -CommentId
  -Content

Tag
  -PostId
  -TagId
  -Name

Classes

public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
}

public class Tag
{
    public int PostId { get; set; }
    public int TagId { get; set; }
    public string Name { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Content { get; set; }
    public int AuthorId { get; set; }
    public List<Tag> Tags { get; set; }
    public List<Comment> Comments { get; set; }
    public Author Author { get; set; }

    public Post()
    {
        this.Comments = new List<Comment>();
        this.Tags = new List<Tag>();
    }
}

public class Comment
{
    public int PostId { get; set; }
    public int CommentId { get; set; }
    public string Content { get; set; }
}

I covered the general pattern here: http://stackoverflow.com/questions/6146918/dapper-sqlmapper-selecting-an-aggregate-object — Sam Saffron, Jul 27 '11 at 11:54
Sam, Thanks for the reference it did help a lot. Though I don't think I was very clear about the my use-case. I have updated the solution to reflect it little more in detail. I actually have both one to one and one to many to many relationships in the graph. Post -> Author, Post -> Comments -> Tags. From the solution you provided Entity -> Childrens can be solved how would you solve Entity -> Child & Entity -> Children -> Children use-cases. — Anil Ali, Jul 28 '11 at 01:57
For now I just ended up mapping int manually rather than trying to generalize the solution — Anil Ali, Jul 28 '11 at 03:04
Can you post some code on how you're mapping that manually? I'm working on a similar problem. — BZink, Jul 28 '11 at 20:56

score 5 · Accepted Answer · answered Aug 01 '11 at 04:48

So I ended up doing something like the following.

public static IEnumerable<Post> Map(this SqlMapper.GridReader reader, Func<Post, int> postKey, Func<Comment, int> commentKey, Func<Tag, int> TagKey, Action<Post, IEnumerable<Comment>> addPostComment, Action<Post, IEnumerable<Tag>> addPostTag)
{
    var posts = reader.Read<Post>().ToList();
    var comments = reader.Read<Comment>().GroupBy(t => commentKey(t)).ToDictionary(g => g.Key, g => g.AsEnumerable());
    var tags = reader.Read<Tag>().GroupBy(t => TagKey(t)).ToDictionary(g => g.Key, g => g.AsEnumerable());
    var authors = reader.Read<Author>().ToList();

    foreach (var post in posts)
    {
        IEnumerable<Tag> posttags;
        if (tags.TryGetValue(postKey(post), out posttags))
        {
            addPostTag(post, posttags);
        }

        IEnumerable<Comment> postcomments;
        if (comments.TryGetValue(postKey(post), out postcomments))
        {
            addPostComment(post, postcomments);
        }

        post.Author = authors.Where(a => a.AuthorId == post.AuthorId).SingleOrDefault();
    }
    return posts;
}

Using the above extension as below.

        var query = "SELECT p.* FROM Post p " +
                    "SELECT c.* FROM Comment c WHERE c.PostId in ( SELECT PostId FROM Post) " +
                    "SELECT t.* FROM Tag t WHERE t.PostId in ( SELECT PostId FROM Post) " +
                    "SELECT a.* FROM Author a WHERE a.AuthorId in ( SELECT AuthorId FROM Post) ";
        List<Post> result = new List<Post>();
        using (var conn = new System.Data.SqlClient.SqlConnection(ConnectionString))
        {
            conn.Open();
            try
            {
                var posts = conn.QueryMultiple(query)
                            .Map
                            (
                                p => p.PostId,
                                c => c.PostId,
                                t => t.PostId,
                                (p, c) => p.Comments.AddRange(c),
                                (p, t) => p.Tags.AddRange(t)
                            );
                if (posts != null && posts.Any())
                {
                    result.AddRange(posts);
                }
            }
            catch (Exception)
            {
                //TODO: Log Exception
                throw;
            }
            conn.Close();
        }

How to select one to many relationship & one to one together using dapper

DB Schema

Classes

1 Answers1