如何使用 dapper 映射多个列表

问题描述

我有三个类用户、订单和;存储在单个表中的项目.订单和项目都与用户有 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}
  ...
}

如您所见，用户对象包含每个相关订单 ID/项目 ID 的列表.

As you can see the User object contains a list of every related orderID/projectID.

现在我想用 Dapper 查询这个.我有这个解决方案，它在 one 列表中工作得很好.但是，如果我尝试查询第二个列表的完整用户对象，我将得到每个结果乘以第一个列表中的结果数.因此，如果用户收到 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;
    }
}

希望有所帮助.

这篇关于如何使用 dapper 映射多个列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！