Map lists of nested objects with Dapper

Question

I'm using Dapper and I have classes like this:

public class Article{
   public int Id { get; set; }
   public string Description{get;set;}
   public Group Group { get; set; }
   public List<Barcode> Barcode {get;set;}
   ...
}

public class Group{
   public int Id { get; set; }
   public string Description {get;set;}
}

public class Barcode{
   public int Id { get; set; }
   public string Code{get;set;}
   public int IdArticle { get; set; }
   ...
}

I can get all information about Article but I would like to know if is possible with one query get also the list of barcodes for each article. Actually what I do is this:

string query = "SELECT * FROM Article a " +
"LEFT JOIN Groups g ON a.IdGroup = g.Id ";

arts = connection.Query<Article, Group, Article>(query,
    (art, gr) =>
    { art.Group = gr;  return art; }
    , null, transaction).AsList();

I also found a good explanation here but I don't understand how to use it in my case, because I have also the Group class. How should I do this with Dapper, is it possible or the only way is to do different steps? Thanks

Answer 1

QueryMultiple is your friend

var query = @"
select a.*, g.* from Article a left join Groups g on g.Id = a.IdGroup    
select * from Barcode";
//NOTE: IdGroup should exists in your Article class.
IEnumerable<Article> articles = null;
using (var multi = connection.QueryMultiple(query)){
    articles = multi.Read<Article, Group, Article>((a, g)=>
            { a.Group = g; return a; });
    if (articles != null) {
      var barcodes = multi.Read<Barcode>().ToList();
      foreach(var article in articles){           
        article.Barcode = barcodes.Where(x=>x.IdArticle == article.Id).ToList(); 
      }
    }
}

That may not be fun especially if you don't have any filters in your query. But I doubt that you will return all Articles. In that case you can filter the Barcode like this (edited sql) > select * from Barcode where Id in @ids. Then include the parameter ids (a list of Article Ids) in the QueryMultiple.

Option2

Or you could just do separate queries:

var query = "select a.*, g.* from Article a left join Groups g on g.Id = a.IdGroup";
var articles = connection.Query<Article, Group, Article>(query,
    (a,g)=> { a.Group = g; return g; }).ToList();
query = "select * from Barcode where IdArticle IN @articleIds";
var articleIds = articles.Select(x=>x.Id);
var barcodes = connection.Query<Barcode>(query, new { articleIds });
foreach(var article in articles){           
    article.Barcode = barcodes.Where(x=>x.IdArticle == article.Id);
}

I prefer the first option.