How do I map multiple lists with dapper

Question

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} ... }

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

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();

I understand why this problem occures (2 inner joins), but I don't really get how to solve it.

Tony Bruni · Accepted Answer · 2017-08-03 19:28:54Z

I also had trouble coming to grips with the fact that Dapper doesn't do this automatically.

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.

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; } }

Hope that helps.

In fact i got a similiar solution, I queried the 1:N relations and the added them with a foreach

Collectives™ on Stack Overflow

How do I map multiple lists with dapper

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related