CODEWITHSUNDEEP
c#linq
TheQ
TheQ

Reputation: 516

Get a list result from another list and a property

I have the following 3 (simplified) model classes, each of which contains a collection of the other:

Group.CollectionOfPermissions
Group.CollectionOfUsers
User.CollectionOfGroups
User.CollectionOfPermissions
Permission.CollectionOfGroups
Permission.CollectionOfUsers

I have a View that is based off a single User.ID, and I want to be able to return the effective permissions for said user.

The effective permissions are based off:

  1. The individual users' permissions, which is simply the User.CollectionOfPermissions property.
  2. The derived permissions of the groups that the user is a part of. That is, for every Group to which the User belongs to, I need to grab those Permissions as well.

Number 1 is obviously as simple as referencing the collection property. Number 2 is where I'm having a bit more trouble with a LINQ selection.

I could write a stored proc along the lines of:

SELECT * FROM PERMISSIONS P WHERE P.ID IN
(SELECT PERMISSION_ID FROM PERMISSION_GROUP_REF PGR WHERE PGR.GROUP_ID IN
(SELECT ID FROM GROUPS G WHERE G.ID IN 
(SELECT GROUP_ID FROM GROUP_USER_REF GUR WHERE GUR.USER_ID IN
(SELECT ID FROM USERS U WHERE U.ID = @USERID))))

But I'd much rather keep this in line with the rest of the project and continue to use LINQ, especially since I want to avoid directly querying the reference tables in code (given that the collections already exist as class properties). How would I approach this kind of LINQ query?

Edit: This is using Entity Framework 6 with Razor 3

Upvotes: 0

Views: 104

Answers (2)

AD.Net
AD.Net

Reputation: 13399

Users.Where(u => u.UserId == userId)
     .SelectMany(u => u.CollectionOfPermissions)
     .Select (cp=>cp.Permission) // you might need to do this too
     .Union(Users.Where(u => u.UserId == userId)
                 .SelectMany(u => u.CollectionOfGroups)
                 .SelectMany(cg => cg.Permission))

May be something like this.

EDIT: For reference, this produces the following SQL (slightly different column names in my test rig):-

SELECT 
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT 
    [UnionAll1].[Permission_Id] AS [C1]
    FROM  (SELECT 
        [Extent1].[Permission_Id] AS [Permission_Id]
        FROM [dbo].[PermissionPersons] AS [Extent1]
        WHERE 1 = [Extent1].[Person_Id]
    UNION ALL
        SELECT 
        [Extent3].[Permission_Id] AS [Permission_Id]
        FROM  [dbo].[PersonGroups] AS [Extent2]
        INNER JOIN [dbo].[PermissionGroups] AS [Extent3] ON [Extent2].[Group_Id] = [Extent3].[Group_Id]
        WHERE 1 = [Extent2].[Person_Id]) AS [UnionAll1]
)  AS [Distinct1]

On another thought, why not query through Permission entity all together?

context.Permissions.Where(p=>
                           p.Groups.Any(gr=>gr.Users.Any(u=>u.UserId == userId)) 
                           || p.Users.Any(u=>u.UserId == userId))
                   .Distinct()

Upvotes: 2

Ashigore
Ashigore

Reputation: 4678

The SQL you posted translates to this:

PERMISSIONS.Where(p =>
  PERMISSION_GROUP_REF.Where(pg =>
    GROUPS.Where(g =>
      GROUP_USER_REF.Where(gu => gu.USER_ID == USERID)
                    .Any(gu => gu.GROUP_ID == g.ID))
          .Any(g => g.ID == pg.GROUP_ID))
        .Any(pg => pg.PERMISSION_ID == p.ID))

Maybe you can simplify it a bit, but this should work.

Upvotes: 0

Related Questions