Dapper Many-to-Many Query

Question

I am trying to write a query to get a user in the system with all of his/her roles. There is a many-to-many relationship between users and roles. The joiner table is SystemUserUserRole with columns UserId and RoleId. My models are below:

SystemUser Model

[Key]
public int UserId { get; set; }

[Required]
[MaxLength(75)]
public string FirstName { get; set; }

[Required]
[MaxLength(75)]
public string LastName { get; set; }

[Required]
[MaxLength(15)]
public string Phone { get; set; }

[Required]
[MaxLength(250)]
public string Email { get; set; }

public virtual List<UserRole> UserRoles { get; set; }

UserRole Model

[Key]
public int RoleId { get; set; }

[Required]
[MaxLength(250)]
public string RoleName { get; set; }

public virtual List<SystemUser> SystemUsers { get; set; }

I am trying to do something below with no luck. Any suggestions on what I am doing wrong.

string query = "SELECT u.*, r.* FROM SystemUser u INNER JOIN SystemUserUserRole ur ON u.UserId = ur.UserId INNER JOIN UserRole r on ur.RoleId = r.RoleId WHERE Email = @email AND IsActive = true;";

SystemUser user = con.Query<SystemUser, UserRole, SystemUser>(query, (SystemUser, UserRole) => { SystemUser.UserRoles = UserRole; return SystemUser; }).First();

score 11 · Answer 1 · answered Jul 16 '15 at 09:43

This will work:

In the SystemUser class, add a constructor that initialises the list:

public SystemUser()
{
    UserRoles = new List<UserRole>();
}

Then tell Dapper that for each joined row, the UserRole should be added to the SystemUser.UserRoles:

SystemUser user = con.Query<SystemUser, UserRole, SystemUser>(query,
    (SystemUser, UserRole) =>
    {
        SystemUser.UserRoles.Add(UserRole);
        return SystemUser;
    },
    splitOn: "RoleId").First();

Note that the final piece is adding the splitOn parameter, because Dapper expects identity columns to be named Id, otherwise you need to tell it the column name explicitly.

Could you comment on related question http://stackoverflow.com/questions/33420442/dapper-multi-mapping-not-working-as-expected? I think it is quite similar, but I'm getting an unexpected result. — itslittlejohn, Oct 29 '15 at 17:07

score 0 · Answer 2 · answered Apr 13 '15 at 16:33

Here is what I ended up doing... feel free to comment or post a better solution if there is one.

I had to do three separate queries. I first grabbed the user object.

SystemUser user = con.Query<SystemUser>("SELECT * FROM SystemUser WHERE Email = @email AND IsActive = true", new { email = email }).First();

I then grabbed the RoleIds from the joiner table:

IEnumerable<int> roleIds = con.Query<int>("SELECT RoleId FROM SystemUserUserRole WHERE UserId = @userId", new { userId = user.UserId });

Finally, I updated the user object and pulled all of the roles associated to that user:

user.UserRoles = con.Query<UserRole>("SELECT * FROM UserRole WHERE RoleID IN @roleIds", new { roleIds = roleIds }).ToList();

You can try the solution presented here https://stackoverflow.com/questions/50786514/many-to-many-in-linq-using-dapper/50786918#50786918 — Steve, Jun 10 '18 at 19:36

score 0 · Answer 3 · answered May 22 '19 at 07:15

you can also try this which worked for me:

var result = con.Query<User, Role, User>(query, (u, r) => 
                    { 
                        u.UserRoles.Add(new UserRole{ User = u, Role=r }); 
                        return u; 
                    }, splitOn:"RoleId");

Dapper Many-to-Many Query

3 Answers3

Linked