如何使用精巧的人映射多个列表 [英] How do I map multiple lists with dapper
问题描述
我有三个班级User,Order&项目存储在单个表中.订单和项目都与用户具有n:n关系. 为了实现这一点,我有两个映射这些关系的交叉表(UserOrders,UserProjects).
I've got three classes User, Order & Project which are stored in single tables. The orders and projects both have a n:n relation with the users. To implement that I've got two crosstables (UserOrders, UserProjects) which map these relations.
public class User
{
public string UserID {get;set;}
public List<string> Orders{get;set;}
public List<string> Projects {get;set;}
}
public class Order
{
public string OrderID {get;set}
...
}
public class Project
{
public string ProjectID {get;set}
...
}
如您所见,User对象包含每个相关的orderID/projectID的列表.
As you can see the User object contains a list of every related orderID/projectID.
现在我想用Dapper进行查询.我有这个解决方案,它可以很好地与一个列表一起使用.但是,如果我尝试为第二个列表查询完整的用户对象,我将得到的每个结果都乘以第一个列表中的结果数量. 因此,如果用户获得3个订单和2个项目,则订单列表会很好,并且该项目列表将包含两个项目3次:
Now I want to query this with Dapper. I' ve got this solution which works pretty fine with one list. But if I try to query the complete user object for the 2nd list I'll get every result multiplied with the number of results in the first list. So if a user got 3 orders and 2 projects the orderlist will be fine and the projectlist will contain both projects 3 times:
var lookup = new Dictionary<string, User>();
var multi = dbDapperFM.Query<User, string, string, User>("SELECT u.*, uo.OrderID, up.ProjectID "+
"FROM User u INNER JOIN UserOrders uo ON u.UserID=uo.UserID "+
"INNER JOIN UserProjects up ON u.UserID=up.UserID", (u, uo, up) =>
{
User user;
if (!lookup.TryGetValue(m.UserID, out user))
lookup.Add(u.UserID, user= u);
if (user.Orders == null)
user.Orders = new List<string>();
user.Orders.Add(uo);
if (user.Projects == null)
user.Projects = new List<string>();
user.Projects.Add(up);
return user;
}, splitOn: "UserID , OrderID, ProjectID ").AsQueryable();
我了解为什么会出现此问题(2个内部联接),但是我真的不知道如何解决它.
I understand why this problem occures (2 inner joins), but I don't really get how to solve it.
推荐答案
我也很难理解Dapper不会自动执行此操作的事实.
I also had trouble coming to grips with the fact that Dapper doesn't do this automatically.
首先,我不确定"splitOn"的逗号分隔值.我以为那里只有一个价值.因此,例如,我的结果集中有多个列名为"ID".
First, I'm not sure about comma-separated values for "splitOn." I thought you could only have one value there. So I have multiple columns in my result set named "ID" for example.
第二,要获得正确的1:N关系,您需要执行一个额外的手动步骤.例如,我参加了2人参加的参与者及其电话号码的会议.然后我必须这样做:
Second, to get the proper 1:N relationships you need to do an extra manual step. For example, I did a 2-table join of participants and their phone numbers. Then I had to do this:
private List<Participant> CollapseResultSet(List<Participant> rawdataset)
{
List<Participant> ret = new List<Participant>();
if (!rawdataset.Any())
{
return ret;
}
else
{
List<string> partIds = rawdataset.Select(p => p.ID).Distinct().ToList();
foreach (string pId in partIds)
{
Participant tmp = rawdataset.Where(p => p.ID == pId).FirstOrDefault();
tmp.PhoneNumbers = rawdataset.Where(p => p.ID == pId).Select(n => n.PhoneNumbers[0]).ToList();
ret.Add(tmp);
}
return ret;
}
}
希望有帮助.
这篇关于如何使用精巧的人映射多个列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!