使用 dapper c# 填充列表对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17362165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill list object using dapper c#
提问by puti26
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 eanArticle. I try to do this query:
我创建了一个文章列表，并使用dapper进行查询。我想填补这个名单与文章的名字，但也有相关的列表EAN文章。我尝试做这个查询：
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#..
并在 C#..
arts = conn.Query<Article>(query, null, transaction).ToList();
but don't work. How i can do? Thank's.. Any suggestion is welcome.
但不工作。我能怎么办？谢谢。。欢迎提出任何建议。
采纳答案by Void Ray
Take a look at Dapper's Multi-Mappingfeature.
看看 Dapper 的Multi-Mapping功能。
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.
Dapper 允许您将一行映射到多个对象。如果您想避免无关的查询和急切的加载关联，这是一个关键功能。
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.
重要说明 Dapper 假定您的 Id 列被命名为“Id”或“id”，如果您的主键不同，或者您想在“Id”以外的点拆分宽行，请使用可选的“splitOn”参数。
回答by Praveen
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;
回答by Xav987
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
- 为每个域对象覆盖 Equals 和 GetHashCode（这可以通过继承自动完成）
- 添加两个扩展以影响子行到其父行
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;
}
}
