Fill list object using dapper c#

问题:

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.

回答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.

回答2:

Try this, will work I think so

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

Also you need to change the query as below.

SELECT ART.ID AS [Id], ART.NAME AS [Name],EAN.EAN AS [BarCode] 
FROM ART,EAN
WHERE ART.ID = EAN.ID_ART;

回答3:

An other solution, with the benefice of using only one request, just two lines of code, and the possibility of chaining the join (exemple with three tables) :

• Override Equals and GetHashCode for each domain object (this can be do automaticly with inheritance)
• Add two extensions for affect the childrens rows to it's parent's row

Request :

var data = connection.Query<Table1, Table2, Table3, Table3>(
        @"  SELECT * FROM Table1
        LEFT JOIN Table2 ON Table1.Id = Table1Id
        LEFT JOIN Table3 ON Table2.Id = Table2Id
        WHERE Table1.Id IN @Ids",
    (t1, t2, t3) => { t2.Table1 = t1; t3.Table2 = t2; return t3; },
    param: new { Ids = new int[] { 1, 2, 3 });

var read = data.GroupBy(t => t.Table2).DoItForEachGroup(gr => gr.Key.Table3s.AddRange(gr)).Select(gr => gr.Key).
    GroupBy(t => t.Table1).DoItForEachGroup(gr => gr.Key.Table2s.AddRange(gr)).Select(gr => gr.Key);

Domain objects :

public class Table1
{
    public Table1()
    {
        Table2s = new List<Table2>();
    }

    public Guid Id { get; set; }
    public IList<Table2> Table2s { get; private set; }

    public override bool Equals(object obj)
    {
        if (obj as Table1 == null) throw new ArgumentException("obj is null or isn't a Table1", "obj");
        return this.Id == ((Table1)obj).Id;
    }

    public override int GetHashCode()
    {
        return this.Id.GetHashCode();
    }
}

public class Table2
{
    public Table2()
    {
        Table3s = new List<Table3>();
    }

    public Guid Id { get; set; }
    public Guid Table1Id
    {
        get
        {
            if (Table1 == null)
                return default(Guid);
            return Table1.Id;
        }
    }
    public IList<Table3> Table3s { get; private set; }
    public Table1 Table1 { get; set; }

    public override bool Equals(object obj)
    {
        if (obj as Table2 == null) throw new ArgumentException("obj is null or isn't a Table2", "obj");
        return this.Id == ((Table2)obj).Id;
    }

    public override int GetHashCode()
    {
        return this.Id.GetHashCode();
    }
}

public class Table3
{
    public Table3()
    {

    }

    public Guid Id { get; set; }

    public Guid Table2Id
    {
        get
        {
            if (Table2 == null)
                return default(Guid);
            return Table2.Id;
        }
    }
    public Table2 Table2 { get; set; }

    public override bool Equals(object obj)
    {
        if (obj as Table3 == null) throw new ArgumentException("obj is null or isn't a Table3", "obj");
        return this.Id == ((Table3)obj).Id;
    }

    public override int GetHashCode()
    {
        return this.Id.GetHashCode();
    }
}

Extensions :

public static class CollectionExtensions
{
    public static void AddRange<T>(this IList<T> that, IEnumerable<T> collection)
    {
        if (that == null)
            throw new ArgumentNullException("that", "that is null.");
        if (collection == null)
            throw new ArgumentNullException("collection", "collection is null.");

        if (that is List<T>)
        {
            ((List<T>)that).AddRange(collection);
            return;
        }

        foreach (T item in collection)
            that.Add(item);
    }

    public static IEnumerable<IGrouping<TKey, TElem>> DoItForEachGroup<TKey, TElem>(this IEnumerable<IGrouping<TKey, TElem>> group, Action<IGrouping<TKey, TElem>> action)
    {
        if (group == null)
            throw new ArgumentNullException("group", "group is null.");
        if (action == null)
            throw new ArgumentNullException("action", "action is null.");

        group.ToList().ForEach(gr => action(gr));
        return group;
    }
}