Linq Combine Left Join Data

Question

Say I have the following database:

Users
-------
UserId (PK)
UserName

Roles
-----
RoleId (PK)
RoleName

UserRoles
---------
UserId (PK)
RoleId (PK)

Users 1-M UserRoles M-1 Roles

Using LinqToSQL, I want to return the following set:

[User1], [Role1, Role2, Role3]
[User2], [Role2, Role3]
[User3], []

Etc...

What is the most efficient way to create this LinqToSql Query?

In addition, if I want to create a filter to return only users that have Role1, what would that entail?

Thx.

Answer 1

Define "efficient". But otherwise...

from u in dataContext.Users
select new { User = u, Roles = u.UserRoles.Select(ur => ur.Role) }

And filtering users by RoleID:

from u in dataContext.Users
where u.UserRoles.Any(ur => ur.RoleID == 1)
select u

Or by some other Role attribute, say, Name:

from u in dataContext.Users
where u.UserRoles.Any(ur => ur.Role.Name == "Role 1")
select u

Combining it all together:

from u in dataContext.Users
select new
{
     User = u,
     Roles = from ur in u.UserRoles
             where ur.RoleID == 1 || ur.Role.Name == "Role 1"
             select ur.Role
}

Answer 2

This is the single query that I would construct to get your desired result set all at once

from u in Users
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
group r by u into grouping
select grouping

It produces the following SQL:

SELECT [t0].[UserId], [t0].[Username]
FROM [Users] AS [t0]
INNER JOIN [UserRoles] AS [t1] ON [t0].[UserId] = [t1].[UserId]
INNER JOIN [Roles] AS [t2] ON [t1].[RoleId] = [t2].[RoleId]
GROUP BY [t0].[UserID], [t0].[Username]
GO

-- Region Parameters
DECLARE @x1 Int = 2
-- EndRegion
SELECT [t2].[RoleId], [t2].[RoleName]
FROM [Users] AS [t0]
INNER JOIN [UserRoles] AS [t1] ON [t0].[UserId] = [t1].[UserId]
INNER JOIN [Roles] AS [t2] ON [t1].[RoleId] = [t2].[RoleId]
WHERE @x1 = [t0].[UserId]

@Pavel's looks like it produces a better SQL statement:

SELECT [t0].[UserId], [t0].[Username], [t2].[RoleId], [t2].[RoleName] (
    SELECT COUNT(*)
    FROM [UserRoles] AS [t3]
    INNER JOIN [Roles] AS [t4] ON [t4].[RoleId] = [t3].[RoleId]
    WHERE [t3].[UserId] = [t0].[UserId]
    ) AS [value]
FROM [Users] AS [t0]
LEFT OUTER JOIN ([UserRoles] AS [t1]
    INNER JOIN [Roles] AS [t2] ON [t2].[RoleId] = [t1].[RoleId]) ON [t1].[UserId] = [t0].[UserId]
ORDER BY [t0].[UserId], [t1].[UserRoleId], [t2].[RoleId]

In terms of efficient, testing is going to be the best way to figure out what most performant approach is for your situation.