如何在实体框架中使用链接表

Question

我一直在试图弄清楚如何使用实体框架来返回这样的查询

{
UserID: 1,
Roles: ["RoleOne","RoleTwo","RoleThree"]
}

我的表如下:

public class User
{
public int? ID { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
}
public class UserRoles
{
public int ID { get; set; }
public int UserID{ get; set; }
public int RoleID { get; set; }
}
public class Roles
{
public int ID { get; set; }
public string? Name { get; set; }
}

我尝试了连接，并研究了在类中创建关系，以及试图找出groupBy，但都没有运气。

如果我错过了一些明显的，请让我知道!谢谢!

Answer 1

您的响应模型应该是这样的:

public class RespModel
{
public int UserID { get; set; }
public List<string> Roles { get; set; }
}

你的查询可以像这样:

var query = from userRolesRec in _context.UserRoles
join user in _context.User on userRolesRec.UserID equals user.ID ?? 0
select new RespModel
{
UserID = user.ID,
Roles = (from roles in _context.Roles 
where userRolesRec.RoleID == roles.ID
select roles.Name).ToList()
}