如何使用三个表在C#中执行外部联接

您想申请 left join 你应该使用 DefaultIfEmpty .

此外,查询中加入的实体是错误的。因为,您应该加入连接实体( UserRole )使用 Role 实体而不是 User 如果要检索分组的角色和对方用户的计数。否则,您永远不知道哪个角色没有用户。

var result = (from r in _context.Roles
    join ur in _context.UserRoles on r.Id equals ur.RoleId into ps
    from ur in ps.DefaultIfEmpty()
    group new { r,ur } by new { r.Id } into grp
    select new UserRoleModel { RoleId = (int)grp.Key.Id, 
    NoOfUsers = grp.Count(t => t.ur != null) }).ToList();

还有,我建议你改变一下

(int)grp.FirstOrDefault().r.RoleId

到

(int)grp.Key.RoleId

查询按分组 RoleId 已经

var result = _context.Roles.Select(x => new UserRoleModel
{
    RoleId = x.Id,
    NoOfUsers = x.UserRoles?.Count() ?? 0
});

试试这个,

var result = (from r in _context.UserRoles
         join u in _context.Users on r.UserId equals u.Id into temp
         from uu in temp.DefaultIfEmpty()
         group new { r, u } by new { r.RoleId } into grp
         select new UserRoleModel { RoleId = (int)grp.FirstOrDefault().r.RoleId, NoOfUsers = grp.Count() }).ToList();