LINQ query list of objects with multiple list of objects in it

April 26, 2022

I have the following table relations

User
  -List<Role>   on User.RoleId = Id
    -RoleDetail on Role.RoleDetailId = Id
    -Practice   on Role.PracticeId = Id

While I do have all these entities linked up in EF, I’m only able to access Roles when I query User with dbConext Include, RoleDetail & Practice are not accessible, which is understandable. Is there any way I can change how I query User to include those information?

If not, how should I join these tables efficiently? I’m not looking to foreach loop on every single user’s multiple roles but a single query request. Any help/hint is appreciated.

Querying User with Role

users = _dbcontext.Users.Include(u => u.Roles)

Joining

var userQuery = _userRepository.GetUser(searchTerm);
var roleQuery = _roleRepository.GetRole();
var test = from user in userQuery join role in roleQuery on user.Roles....

>Solution :

You need to use two Include on roles for getting all data like this:

users = _dbcontext.Users.Include(u => u.Roles).ThenInclude(x=>x.RoleDetail)
                        .Include(u => u.Roles).ThenInclude(x=>x.Practice)
                        .AsSplitQuery(); //highly recommend if you are using .Net >=5