I would achieve this goal using EFCore method syntax
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?
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()
.