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.

• Iman: Is it good practice to write .Where(acc => acc.PersonEntityId == personEntityId && acc.AccountAdminPositionAssignments.Any(aap => !aap.IsLocked) && ...).SelectMany(acc => acc.AccountAdminPositionAssignments.SelectMany(aap => ... instead of yours?
• ChrisWue: @Iman: Not really sure. I like to filter at the place where I'm selecting the data from, but if your version yields a better query or you simply like it better then go for it.