How Do I perform an outer Join in C# using three tables

Question

I want to get the list of roles and the total number of users in each of the roles. So far, this works:

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

But it does not display roles that don't have any users in it. I have 12 roles, 7 of them have been assigned to at least one user, while the remaining 5 have not. I want to display all the roles with the number of users assigned to them, but if the roles have no users assigned to them (like those 5) I want it to return 0 as the number of users. Thanks

I think you need a `DefaultIfEmpty` for an outer join https://stackoverflow.com/questions/3404975/left-outer-join-in-linq, where is the third table in your example? — Jodrell, Jan 19 '18 at 09:41
When I see questions like this, where you need to go to great lengths to write linq which is almost like SQL, I wonder, why not just write the SQL, it will run faster and be more predictable. — Jodrell, Jan 19 '18 at 09:44
Three tables? I only see two. Doesn't `UserRole` have a property `User`? You shouldn't have to code joins explicitly. — Gert Arnold, Jan 19 '18 at 22:11

lucky · Accepted Answer · 2018-01-19T15:20:33.757

You want to apply left join and you should join them using DefaultIfEmpty.

Also, the entities which is joined in the query is wrong. Because, you should join junction entity (UserRole) with Role entity instead of User if you want to retrieve the grouped roles and count of opposite users. Otherwise, you never know which role doesn't have 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();

Also, I suggest you to change

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

to

(int)grp.Key.RoleId

The query is grouped by RoleId already.

Also, I have additional note. I strongly suggest you to define navigation properties and use them. If you would have them in the entities, the query would be simplier;

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

I Tried this, it still generates only roles with with users assigned to them. I have 12 roles, 7 of them have been assigned to at least one user, while the remaining 5 have not. I want to display all the roles with the number of users assigned to them, but if the roles have no users assigned to them (like those 5) I want it to return 0 as the number of users. Thanks — user8107351, Jan 19 '18 at 10:17

score 0 · Answer 2 · answered Jan 19 '18 at 09:40

Try this,

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

How Do I perform an outer Join in C# using three tables

2 Answers2