Fill list object using dapper c#

Question

I have two table in my database like this:

And i have this class:

   class Ean
   {
        public string Code{ get; set; }
   }

   class Article
   {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Ean> BarCode { get; set; }
   }

List<Article> arts = new List<Article>();

I create a list of article , and with a query using dapper. I would like to fill this list with the name of the article but also with a list of related ean Article. I try to do this query:

SELECT ART.ID AS ID, ART.NAME AS NAME,EAN.EAN AS BARCODE
FROM ART,EAN
WHERE ART.ID = EAN.ID_ART;

and in c#..

arts = conn.Query<Article>(query, null, transaction).ToList();

but don't work. How i can do? Thank's.. Any suggestion is welcome.

Answer 1

Take a look at Dapper's Multi-Mapping feature.

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:

var sql = 
@"select * from #Posts p 
left join #Users u on u.Id = p.OwnerId 
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);

Important note Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.