Query users & roles (ASP.NET identity) via Linq

Question

I want to get patients and doctors from database. I am using ASP.NET Identity.

The relevant tables are AspNetUsers, AspNetRolesand AspNetUserRoles

And this is my query:

var result = from pateints in context.Users
             let pEmail = pateints.Email
             from doctors in context.Users
             from roles in context.Roles
             where pateints.Roles.Any(r => r.RoleId == roles.Id && roles.Name == "Patient")
             where doctors.Roles.Any(r => r.RoleId == roles.Id && roles.Name == "Doctor") 
             select new { doctors.Email, pEmail };

I tried my best to find the problem, but this query returns nothing.

Answer 1

You just need to join all three entities and then apply filter on role name like below -

var result = from u in context.Users
             join ur context.UserRoles on u.Userid equal ur.Userid
             join r in context.Roles on r.Roleid equal ur.Roleid
             where r.Name == "Doctor" || r.Name == "Patient"
             select new {UserName = u.UserName, Email = u.Email, Role = r.Name };

The sample output will be like below -

UserName        Email           Role
abc xyz         [email protected]     Doctor
doc xyz         [email protected]     Doctor
pqr xyz         [email protected]     Patient
lmn xyz         [email protected]     Patient