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

von v. · Accepted Answer · 2020-12-10T14:52:58.277

4

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.

edited Dec 10 '20 at 14:52

answered Aug 19 '15 at 04:00

von v.

16,868
4
60
84

Hi von, on the second option at row 'var articleIds = articles.Select(x=>x.Id);' is not possible do select appear an error message. How you did it if is not possible ? – puti26 Aug 21 '15 at 07:53
I'm not 100% but I know that Dapper returns an empty list if there's no result for a SELECT. So a `.Select(expression)` should not throw a null exception. What is the error you're getting? – von v. Aug 21 '15 at 12:55
Is not an error, it looks like articles.Select the option "Select" doesn't appear in the list of operation i can do with articles. – puti26 Aug 24 '15 at 06:52
`articles` in the code is an IEnumerable object. If you have a `using System.Linq` then a `.Select` with it should work. – von v. Aug 24 '15 at 10:07
Oh i forgot to check "using System.Linq;". It works now ! Thanks – puti26 Aug 24 '15 at 10:23
Glad to be of service ;) – von v. Aug 24 '15 at 12:06
I think `x.IdArticle = article.Id` needs to be `x.IdArticle == article.Id` – Gevo12321 Nov 16 '20 at 22:53

Map lists of nested objects with Dapper

1 Answers1

QueryMultiple is your friend

Option2