Create a left join query without navigation property

entity-framework entity-framework-core

Question

I would achieve this goal using EFCore method syntax

  • Given an user retrieve all roles and set IsInRole = 1 if the given user has the role, otherwise 0

in tsql is something like

CREATE TABLE USER(USERNAME VARCHAR(200) PRIMARY KEY);
CREATE TABLE ROLE(ROLENAME VARCHAR(200) PRIMARY KEY);
CREATE TABLE USERROLE(USERNAME VARCHAR(200), ROLENAME VARCHAR(200));

INSERT INTO USER VALUES('LELU');
INSERT INTO ROLE VALUES('ROLE1');
INSERT INTO ROLE VALUES('ROLE2');
INSERT INTO USERROLE VALUES('LELU', 'ROLE1');

SELECT R.ROLENAME, CASE WHEN T.ROLEID IS NULL THEN 0 ELSE 1 END ISINROLE
FROM ROLE R
LEFT JOIN (
  SELECT UR.ROLENAME ROLEID
  FROM USERROLE UR
  WHERE UR.USERNAME='LELU'
) T ON T.ROLEID=R.ROLENAME 

ROLENAME                ISINROLE
-------------------------------------
ROLE1                   1
ROLE2                   0

I would like to achive this goal using EF core without navigation properties.

I have the following entities

public class User 
{
    [Key]
    public string Username {get; set;}
}

public class Role 
{
    [Key]
    public string Rolename {get; set;}
}

public class UserRole 
{
    public string Username {get; set;}
    public string Rolename {get; set;}
}

where UserRole (Username, Rolename) are primary keys

public class MyDbContext : DbContext 
{
    public DbSet<User> Users {get; set;}
    public DbSet<Role> Roles {get; set;}
    public DbSet<UserRole> UsersRoles {get; set;}
    ...
}

I do some research online but i didn't find anything that can accomplish a left join without navigation properties.

How can I achive this goal without using navigation property neither injecting raw query in EFCore?

1
2
12/18/2019 7:14:22 AM

Accepted Answer

It is possible without navigation properties. It's not pretty by any stretch and depending on what details you want from the associated entities it will get a lot uglier. However, to get a list of Roles with a flag for whether the provided user is assigned to them or not:

using (var context = new MyDbContext())
{
    var roles = context.Roles
        .SelectMany(r => context.UserRoles.Where(ur => ur.RoleName == r.RoleName 
             && ur.UserName == userName).DefaultIfEmpty(),
             (r, ur) => new
             {
                 RoleName = r.RoleName,
                 IsInRole = ur != null
             }).ToList();
}

That would return each role, and whether the provided userName value was assigned to that role using a Left join via the DefaultIfEmpty().

2
12/17/2019 11:40:35 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow