title: Dapper Many-to-Many Query
body: <p>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 <code>SystemUserUserRole</code> with columns <code>UserId</code> and <code>RoleId</code>.  My models are below:</p>
  
  <p>SystemUser Model</p>
  
  <pre><code>[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&lt;UserRole&gt; UserRoles { get; set; }
  </code></pre>
  
  <p>UserRole Model</p>
  
  <pre><code>[Key]
  public int RoleId { get; set; }
  
  [Required]
  [MaxLength(250)]
  public string RoleName { get; set; }
  
  public virtual List&lt;SystemUser&gt; SystemUsers { get; set; }
  </code></pre>
  
  <p>I am trying to do something below with no luck.  Any suggestions on what I am doing wrong.</p>
  
  <pre><code>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&lt;SystemUser, UserRole, SystemUser&gt;(query, (SystemUser, UserRole) =&gt; { SystemUser.UserRoles = UserRole; return SystemUser; }).First();
  </code></pre>