Dapper多对多查询 [英] Dapper Many-to-Many Query
问题描述
我正在尝试编写一个查询,以使系统中的用户拥有他/她的所有角色.用户和角色之间存在多对多关系.联接器表为SystemUserUserRole
,其列为UserId
和RoleId
.我的模型如下:
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模型
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模型
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();
推荐答案
这将起作用:
在SystemUser类中,添加一个初始化列表的构造函数:
In the SystemUser class, add a constructor that initialises the list:
public SystemUser()
{
UserRoles = new List<UserRole>();
}
然后告诉Dapper,对于每个连接的行,应将UserRole添加到SystemUser.UserRoles:
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();
请注意,最后一步是添加splitOn
参数,因为Dapper希望标识列被命名为Id
,否则您需要明确告诉它列名.
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.
这篇关于Dapper多对多查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!