如何使用Dapper和多重映射按特定顺序返回一对多记录？ [英] How do I return one-to-many records in a specific order with Dapper and multi-mapping?
问题描述
来自 Github ：
Dapper允许您将单行映射到多个对象。如果您要避免不必要的查询并渴望加载
关联，则这是
的关键功能。
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.
示例：
考虑2类：发布和用户
> class Post {
> public int Id { get; set; }
> public string Title { get; set; }
> public string Content { get; set; }
> public User Owner { get; set; } }
>
> class User {
> public int Id { get; set; }
> public string Name { get; set; } }
现在让我们说我们要映射一个查询同时加入帖子
和users表。到目前为止，如果我们需要合并两个查询的结果，则
，我们需要一个新的
对象来表示它，但是在这种情况下，将
User对象放在
Now let us say that we want to map a query that joins both the posts and the users table. Until now if we needed to combine the result of 2 queries, we'd need a new object to express it but it makes more sense in this case to put the User object inside the Post object.
执行此操作时（我的类名不同，但结构相同），我得到了一个Post和一个User，一个帖子和一个用户。我使用的是Web API，所以这就是JSON（如果有的话）。如果我在Management Studio中直接执行SQL，这就是我看到的方式，那么您会得到很多行和相应的用户记录
When I do this (My classes are different names, but same construct), I get a Post and a User, a Post and a User. I'm using the Web API, so this is all JSON, if that matters. This is the way I'd see it if I did straight SQL in the Management Studio, you get the many rows and the corresponding User records
如果我想发送该怎么办返回具有一次用户的JSON和数组中的所有帖子，然后返回下一个用户，帖子的数组等。
What if I want to send back the JSON that has the User once and all the posts in an array, then the next User, array of posts, etc.
id title content id name
1 Article1 Content1 55 Smith
2 Article2 Content2 55 Smith
3 Article3 Content3 55 Smith
我得到了一遍又一遍的JSON，其中包含用户信息（按预期，但不需要）。
I get the JSON back that has the User information over and over (as expected but not wanted). It's backwards.
我想要的是一个具有这样格式的JSON对象（我认为这是正确的）：
What I want is a JSON object that has a format like this (I think this is correct):
{
"User": 55,
"Name": "Smith",
"Post": [
{
"id": 1,
"title": "title1",
"content":"MyContent1"
},
{
"id": 2,
"title": "title2",
"content":"MyContent2"
},
{
"id": 3,
"title": "title3",
"content":"MyContent2"
}
]
}
我该怎么做？现在我反过来了。我以为我会改变周围的课程，但是我没有因为Github上的说明（更有意义的部分）而没有这么做。我正在使用它，
How do I do this? Right now I get the reverse. I thought I would simply change the classes around, but I did not because of the instructions on Github, the "makes more sense" part. I am using this,
(List<Post>)db.Query<Post, User, Paper>(sqlString, (post, user) => { post.user = user; return post; }, splitOn: "id");
我知道我不需要splitOn，但是在我真正的查询中，该名称不同于
I know I don't need the splitOn here, but in my real query the name is different than id.
这非常接近：
> https://www.tritac.com/developers-blog/dapper-net-by-example/
public class Shop {
public int? Id {get;set;}
public string Name {get;set;}
public string Url {get;set;}
public IList<Account> Accounts {get;set;}
}
public class Account {
public int? Id {get;set;}
public string Name {get;set;}
public string Address {get;set;}
public string Country {get;set;}
public int ShopId {get;set;}
}
var lookup = new Dictionary<int, Shop>()
conn.Query<Shop, Account, Shop>(@"
SELECT s.*, a.*
FROM Shop s
INNER JOIN Account a ON s.ShopId = a.ShopId
", (s, a) => {
Shop shop;
if (!lookup.TryGetValue(s.Id, out shop)) {
lookup.Add(s.Id, shop = s);
}
if (shop.Accounts == null)
shop.Accounts = new List<Account>();
shop.Accounts.Add(a);
return shop;
},
).AsQueryable();
var resultList = lookup.Values;
它是第一个对象标识符。不知道我是否可以那样使用它。但这确实像我要的那样处理了很多书，而且我不必创建一个特殊的对象。最初，它应该在Google Code上，但是我在Github上找不到此测试。
It makes the first object identifier. Not sure if I can use it like that or not. But this does do the array of books like I was asking, and I did not have to create a special object. Originally, it was supposed to be on Google Code, but I couldn't find this test on Github.
推荐答案
另一个选择是使用。 QueryMultiple
[Test]
public void TestQueryMultiple()
{
const string sql = @"select UserId = 55, Name = 'John Doe'
select PostId = 1, Content = 'hello'
union all select PostId = 2, Content = 'world'";
var multi = _sqlConnection.QueryMultiple(sql);
var user = multi.Read<User>().Single();
user.Posts = multi.Read<Post>().ToList();
Assert.That(user.Posts.Count, Is.EqualTo(2));
Assert.That(user.Posts.First().Content, Is.EqualTo("hello"));
Assert.That(user.Posts.Last().Content, Is.EqualTo("world"));
}
更新：
返回多个用户及其帖子：
To return multiple users and their posts:
[Test]
public void TestQueryMultiple2()
{
const string sql = @"select UserId = 55, Name = 'John Doe'
select UserId = 55, PostId = 1, Content = 'hello'
union all select UserId = 55, PostId = 2, Content = 'world'";
var multi = _sqlConnection.QueryMultiple(sql);
var users = multi.Read<User>().ToList();
var posts = multi.Read<Post>().ToList();
foreach (var user in users)
{
user.Posts.AddRange(posts.Where(x => x.UserId == user.UserId));
}
Assert.That(users.Count, Is.EqualTo(1));
Assert.That(users.First().Posts.First().Content, Is.EqualTo("hello"));
Assert.That(users.First().Posts.Last().Content, Is.EqualTo("world"));
}
这篇关于如何使用Dapper和多重映射按特定顺序返回一对多记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
