0

I have three tables, Roles, Users, and connecting table. I want to show all roles, and for particular user it's name (or if he is member of it). SQL below with nested joins (for SQL Server). How to write LINQ in C#? Could someone please help to translate SQL -> LINQ? I'm using Entity Framework 6 for .Net 4.7.2, and same names for the tables under db context: DbSet<tRole> tRoles

create table tRole (id int, name varchar(255))
insert into  tRole values (1,'user')
insert into  tRole values (2,'admin')

create table tUser (id int, name varchar(255))
insert into  tUser values (1,'john')
insert into  tUser values (2,'sam')
insert into  tUser values (3,'amanda')

create table tRoleUser (roleId varchar(255), userId varchar(255))
insert into tRoleUser values (1,1)  -- user john
insert into tRoleUser values (1,2)  -- user sam
insert into tRoleUser values (2,2)  -- admin sam
-- amanda not user, and not admin

select 
    r.name [role name],
    ru.userid,
    u.name [user name]
from tRole r
left join tRoleUser ru
     join tUser u on u.id = ru.userid and u.name = 'john'
   on r.id = ru.roleid

Result for john:

user    1       john
admin   NULL    NULL

Result for amanda:

user    NULL    NULL
admin   NULL    NULL

Result for sam:

user    2   sam
admin   2   sam
sam sergiy klok
  • 526
  • 7
  • 17
  • Only tag the technologies you are wanting experts in SQL Server experts can't help you here. – Dale K Jul 20 '23 at 20:06
  • What LINQ are you using: LINQ to SQL / EF 4.x / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x / 7.x / 8.x? – NetMage Jul 21 '23 at 20:46
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Jul 21 '23 at 20:46

1 Answers1

1

Below is one of my attempts to solve it..

var roles = from r in db.tRoles
            join uru in (
                from ur in db.tUserRoles 
                join u in db.tUsers on ur.UserId equals u.Id
                into j1 from j1r in j1
                where j1r.UserName == id
                select (new {ur.RoleId, ur.UserId, j1r.UserName})
            )
            on r.Id equals uru.RoleId into j2
            from j2r in j2.DefaultIfEmpty()
            select (new
            {
                RoleName = r.Name,
                RoleId = r.Id,
                UserId = j2r.UserId,
                UserName = j2r.UserName
            });

var userRoles = roles
    .ToList()  // let's execute LINQ to SQL
    .Select(x => new 
    {
        RoleId = x.RoleId,
        RoleName = x.RoleName,
        UserId = x.UserId,
        UserName = x.UserName
    }).ToList();
sam sergiy klok
  • 526
  • 7
  • 17
  • Move this NON answer to original question. And add Model entities. LINQ Queries are built from classes and their navigation properties. Normally it is not needed to use joins at all. – Svyatoslav Danyliv Jul 21 '23 at 13:15