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-10 14:52:58Z

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, 2020 at 14:52

answered Aug 19, 2015 at 4:00

von v.

17.1k5 gold badges62 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

puti26 Over a year ago

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 ?

von v. Over a year ago

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?

puti26 Over a year ago

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.

von v. Over a year ago

articles in the code is an IEnumerable object. If you have a using System.Linq then a .Select with it should work.

puti26 Over a year ago

Oh i forgot to check "using System.Linq;". It works now ! Thanks

|

Collectives™ on Stack Overflow

Map lists of nested objects with Dapper

1 Answer 1

QueryMultiple is your friend

Option2

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

QueryMultiple is your friend

Option2

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related