Dapper Many-to-Many Query

asp.net-mvc-4 dapper

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

Popular Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why