How to write below query using EF?

Dave Bell

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')

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar3y ago

Accepted 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 Bell3y ago
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()~~;
0
Rajkiran Swain3y ago
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();

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

Jay Pankhaniya3y ago

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