Map lists of nested objects with Dapper
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.
puti26
Updated on July 26, 2022Comments
-
puti26 almost 2 years
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