0

I have three tables "Applications", "UserPermissions" and "ADPermissions".

Applications table contains list of applications. UserPermissions table defines which user is allowed to manage a particular application. Similarly "ADPermissions" table defines which AD Group has permissions to manage the application.

When a user login to an Administration Application (a web site), I have to display all the applications that he has access to based on his alias and all the AD groups that he belongs to. I am trying to write a Linq query like below but this gives me duplicate results. Also I have few other questions.

//List of groups from AD
var groups = (from g in UserPrincipal.Current.GetGroups()
              select g.Name).ToArray<String>();

IdentityContext context = new IdentityContext();

var apps = 
       (from a in context.Applications
        join up in context.UserPermissions on a.Id equals up.ClientId into appUsers
        from up in appUsers.DefaultIfEmpty()
        join gp in context.ADPermissions on a.Id equals gp.ClientId into appGroups
        from gp in appGroups.DefaultIfEmpty()
        select new
        {
            Id = a.Id,
            ClientId = a.ClientId,
            DisplayName = a.ClientName,
            Enabled = a.Enabled,
            ClientUri = a.ClientUri,
            UserPermissions = a.UserPermissions,
            GroupPermissions = a.ADPermissions
        }).ToList().Distinct();
  1. How can I get unique results for this scenario?
  2. How can I apply filter to the above group based on the AD group names that I have instead of filtering the data after retrieving form SQL server?
  3. Is this a right approach?

Please help.

kaveman
  • 4,339
  • 25
  • 44
user3731783
  • 718
  • 1
  • 7
  • 31
  • Is there a reason you need both results at once? Wouldn't it be better to grab the userPermissions in one pass and the ADPermissions in a second one? – Guvante Feb 27 '15 at 22:42
  • 1
    as for getting unique results: consider that after you have called `ToList()`, the call to `Distinct()` is acting on a list of anonymous objects - [since `Distinct` uses the default equality comparer](https://msdn.microsoft.com/en-us/LIBRary/bb348436(v=vs.110).aspx), it is doubtful you'll get the results you're expecting. – kaveman Feb 27 '15 at 22:42

1 Answers1

0

You can use group by instead of distinct, click here to see an exapmle

Community
  • 1
  • 1
Bilel Chaouadi
  • 903
  • 1
  • 10
  • 28