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();

How to write below query using EF?

Answers (5)