Nested select Linq

Question

Is there a simpler and optimized way to calculate listAdminCorePriveleges and privileges ?

public string[] GetPrivilegesForUserByPersonEntityId(int personEntityId)
    {
        var listPrivileges = new List<string>();
        var listAdminCorePriveleges = (_accountRepository.AsQueryable()
          .Where(acc => acc.PersonEntityId == personEntityId)
          .Select(acc => acc.AccountAdminPositionAssignments.Where(aap => aap.IsLocked == false && aap.IsObsolete == false).Select(aap => aap.AdminPosition.AdminType.AdminPrivileges))
          ).AsEnumerable();

        foreach (var adminCorePriveleges in listAdminCorePriveleges)
        {
            foreach (var adminCorePrivelege in adminCorePriveleges)
            {
                listPrivileges.Add(adminCorePrivelege.Name); 
            }
        }
        string[] privileges = listPrivileges.Distinct().ToArray();
        return privileges;
    }

Answer 1

To know for sure what exactly is inefficient you'd need to hook up a profiler.

Code wise you should be able to do it all in one query if I'm not mistaken:

return _accountRepository.AsQueryable()
            .Where(acc => acc.PersonEntityId == personEntityId)
            .SelectMany(acc => acc.AccountAdminPositionAssignments
                                  .Where(aap => !aap.IsLocked && !aap.IsObsolete)
                                  .SelectMany(aap => aap.AdminPosition.AdminType.AdminPrivileges.Select(p => p.Name)))
            .Distinct()
            .ToArray();

This will in theory also move the name selection and distinct filtering to the database which means that probably slightly less data has to be transferred. Adds a bit more work for the database server but that kind of stuff is what they should do well.