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.
Author
-AuthorId
-Name
Post
-PostId
-Content
-AuthorId
Comment
-PostId
-CommentId
-Content
Tag
-PostId
-TagId
-Name
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; }
}
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With