4

The answer here contained the following query:

var query = from role in _db.Roles
            where role.Name == roleName
            from userRoles in role.Users
            join user in _db.Users
            on userRoles.UserId equals user.Id
            select user;

How would I reproduce the same query using Linq method syntax?

Community
  • 1
  • 1
Yaron
  • 1,867
  • 20
  • 16

2 Answers2

3
var query = _db.Roles
    .Where(role => role.Name == roleName)
    .SelectMany(role => role.Users)
    .Join(_db.Users, userRole => userRole.UserId, user => user.Id, (role, user) => user);

Some explanation

var query = from role in _db.Roles
        where role.Name == roleName // this will be translated to .Where
        from userRoles in role.Users // this is .SelectMany
        join user in _db.Users // this is .Join
        on userRoles.UserId equals user.Id // second and third arguments of .Join
        select user; // last argument of .Join
hazzik
  • 13,019
  • 9
  • 47
  • 86
  • Am I missing something? That's a literal translation of the query, sure, but what's the point of the third line? Edit: does role really have a navigation property called Users that doesn't consist of Users? – ChrisV Jun 29 '15 at 23:39
  • As I understand from initial query (see "on userRoles.UserId equals user.Id" part) that Role has collection of UserRole {UserId, RoleId} objects. – hazzik Jun 29 '15 at 23:42
  • @ChrisV also, it is not literal translation. The literal translation is in Ricky's answer. – hazzik Jun 29 '15 at 23:43
  • @ChrisV original question is about AspNet Identity. See the sources: https://github.com/aspnet/Identity/blob/dev/src/Microsoft.AspNet.Identity.EntityFramework/IdentityRole.cs#L52 and https://github.com/aspnet/Identity/blob/dev/src/Microsoft.AspNet.Identity.EntityFramework/IdentityUserRole.cs – hazzik Jun 29 '15 at 23:48
0

This is the method syntax version

    var query =
        _db.Roles.Where(role => role.Name == roleName)
            .SelectMany(role => role.Users, (role, userRoles) => new {role, userRoles})
            .Join(_db.Users, @t => userRoles.UserId, user => user.Id, (@t, user) => user);
Ricky Gummadi
  • 4,559
  • 2
  • 41
  • 67
  • You dont need resultSelector with anonymous type on SelectMany, because you do use only userRoles from it. – hazzik Jun 29 '15 at 23:38