How to write below query using EF?

Question

I have injected IdentityUser to User table. EF created N:N UserStudent table.

SELECT TOP 
      [Email]

  FROM [model].[dbo].[AspNetUsers] A, [model].[dbo].[AspNetUserRoles] B 
  where
  A.Id = B.UserId and B.RoleId = 'parent'
  and
 A.Id = (select UserId from [model].[dbo].[UserStudent] where StudentId = 'X')

Jignesh Kumar · Answer

Hello Dave Bell Please try this one var result = ( from user in context.AspNetUsers join userRole in context.AspNetUserRoles on user.Id equals userRole.UserId where userRole.RoleId == parent && user.Id == (from userStudent in context.UserStudent where userStudent.StudentId == X select userStudent.UserId).FirstOrDefault() select user.Email ).FirstOrDefault();

Dave Bell · Answer

Thanks Jignesh. This works perfectly. Could you please help on how to tweat below for in (as I might get more than 1 records - teachers and parent) var result = ( from user in context.AspNetUsers join userRole in context.AspNetUserRoles on user.Id equals userRole.UserId where userRole.RoleId == parent && user.Id ==( in? ) (from userStudent in context.UserStudent where userStudent.StudentId == X select userStudent.UserId).FirstOrDefault() select user.Email ) .FirstOrDefault() ;

Rajkiran Swain · Answer

var result = (from u in context.AspNetUsers join ur in context.AspNetUserRoles on u.Id equals ur.UserId join us in context.UserStudent on u.Id equals us.UserId where ur.RoleId == parent && us.StudentId == X select u.Email).FirstOrDefault();

Dave Bell · Answer

But UserRoles is not in my dbContext. It was created by injecting IdentityUser. I think they are accessible use UserManager

Jay Pankhaniya · Answer

var userEmails = dbContext.Users .Where(u => u.UserRoles.Any(ur => ur.RoleId == parent) && u.UserStudents.Any(us => us.StudentId == X)) .Select(u => u.Email) .ToList();