如何选择一对多关系&使用 dapper 一对一地在一起

Question

我有以下类和数据库架构。我正在尝试使用 dapper 从数据库中查询这些数据，这将水合完整的对象图。我查看了各种 SO 问题和测试，但无法真正弄清楚如何做到这一点。

数据库架构

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

Answer 1

所以我最终做了如下的事情。

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

使用上面的扩展如下。

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