How do I return one-to-many records in a specific

Question

From Github:

Dapper allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.

Example:

Consider 2 classes: Post and User

> class Post {
>     public int Id { get; set; }
>     public string Title { get; set; }
>     public string Content { get; set; }
>     public User Owner { get; set; } }
> 
> class User {
>     public int Id { get; set; }
>     public string Name { get; set; } } 

Now let us say that we want to map a query that joins both the posts and the users table. Until now if we needed to combine the result of 2 queries, we'd need a new object to express it but it makes more sense in this case to put the User object inside the Post object.

When I do this (My classes are different names, but same construct), I get a Post and a User, a Post and a User. I'm using the Web API, so this is all JSON, if that matters. This is the way I'd see it if I did straight SQL in the Management Studio, you get the many rows and the corresponding User records

What if I want to send back the JSON that has the User once and all the posts in an array, then the next User, array of posts, etc.

id   title    content   id    name
1    Article1 Content1  55  Smith
2    Article2 Content2  55  Smith
3    Article3 Content3  55  Smith

I get the JSON back that has the User information over and over (as expected but not wanted). It's backwards.

What I want is a JSON object that has a format like this (I think this is correct):

{
  "User": 55,
  "Name": "Smith",
  "Post": [
    {
      "id": 1,
      "title": "title1",
      "content":"MyContent1"
    },
    {
      "id": 2,
      "title": "title2",
      "content":"MyContent2"
    },
    {
      "id": 3,
      "title": "title3",
      "content":"MyContent2"
    }
  ]
}

How do I do this? Right now I get the reverse. I thought I would simply change the classes around, but I did not because of the instructions on Github, the "makes more sense" part. I am using this,

(List<Post>)db.Query<Post, User, Paper>(sqlString, (post, user) => { post.user = user; return post; }, splitOn: "id");

I know I don't need the splitOn here, but in my real query the name is different than id.

This is pretty close:

https://www.tritac.com/developers-blog/dapper-net-by-example/

public class Shop {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
  public IList<Account> Accounts {get;set;}
}

public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get;set;}
}

var lookup = new Dictionary<int, Shop>()
conn.Query<Shop, Account, Shop>(@"
                    SELECT s.*, a.*
                    FROM Shop s
                    INNER JOIN Account a ON s.ShopId = a.ShopId                    
                    ", (s, a) => {
                         Shop shop;
                         if (!lookup.TryGetValue(s.Id, out shop)) {
                             lookup.Add(s.Id, shop = s);
                         }
                         if (shop.Accounts == null) 
                             shop.Accounts = new List<Account>();
                         shop.Accounts.Add(a);
                         return shop;
                     },
                     ).AsQueryable();

var resultList = lookup.Values;

It makes the first object identifier. Not sure if I can use it like that or not. But this does do the array of books like I was asking, and I did not have to create a special object. Originally, it was supposed to be on Google Code, but I couldn't find this test on Github.

Answer 1

Another option is to use .QueryMultiple

    [Test]
    public void TestQueryMultiple()
    {
        const string sql = @"select UserId = 55, Name = 'John Doe'
                    select PostId = 1, Content = 'hello' 
                    union all select PostId = 2, Content = 'world'";

        var multi = _sqlConnection.QueryMultiple(sql);
        var user = multi.Read<User>().Single();
        user.Posts = multi.Read<Post>().ToList();

        Assert.That(user.Posts.Count, Is.EqualTo(2));
        Assert.That(user.Posts.First().Content, Is.EqualTo("hello"));
        Assert.That(user.Posts.Last().Content, Is.EqualTo("world"));
    }

Update:

To return multiple users and their posts:

    [Test]
    public void TestQueryMultiple2()
    {
        const string sql = @"select UserId = 55, Name = 'John Doe'
                    select UserId = 55, PostId = 1, Content = 'hello' 
                    union all select UserId = 55, PostId = 2, Content = 'world'";

        var multi = _sqlConnection.QueryMultiple(sql);
        var users = multi.Read<User>().ToList();
        var posts = multi.Read<Post>().ToList();

        foreach (var user in users)
        {
            user.Posts.AddRange(posts.Where(x => x.UserId == user.UserId));
        }

        Assert.That(users.Count, Is.EqualTo(1));
        Assert.That(users.First().Posts.First().Content, Is.EqualTo("hello"));
        Assert.That(users.First().Posts.Last().Content, Is.EqualTo("world"));
    }

Answer 2

Since your SQL query is returning the flat records, i suggest you create a flat POCO and use dapper to map the result set to a collection of this. Once you have data in this collection, you can use LINQ GroupBy method to group it the way you want.

Assuming you have classes like

public class User
{
  public int Id { set;get;}
  public string Name { set;get;}
  public IEnumerable<Post> Posts { set;get;}
}
public class Post
{
  public int Id { set;get;}
  public string Title{ set;get;}
  public string Content { set;get;}
}

Now create the POCO for the flat result set row

public class UserPost
{
    public int Id { set; get; }
    public string Title { set; get; }
    public string Content { set; get; }

    public int UserId { set; get; }
    public string Name { set; get; }
}

Now update your SQL query to return a result set with column name matching the above properties.

Now use Dapper to get the flat records

var userposts= new List<UserPost>();
using (var conn = new SqlConnection("YourConnectionString"))
{
    userposts = conn.Query<UserPost>(query).ToList();
}

Now apply GroupBy

var groupedPosts = userposts.GroupBy(f => f.UserId, posts => posts, (k, v) =>
    new User()
    {
        UserId = k,
        Name = v.FirstOrDefault().Name,
        Posts = v.Select(f => new Post() { Id = f.Id, 
                                           Title= f.Title, 
                                           Content = f.Content})
    }).ToList();