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

Question

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....

Please post what have you tried with EF to get data? user and role have one-to-one relation? — sa-es-ir
– sa-es-ir, Commented Apr 26, 2022 at 7:05
yes Role has a one-to-one relation with RoleDetail & Practice. I'll post what I have so far — joseph.c
– joseph.c, Commented Apr 26, 2022 at 7:08

sa-es-ir · Accepted Answer · 2022-04-26 07:36:34Z

1

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

answered Apr 26, 2022 at 7:36

sa-es-ir

5,1902 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joseph.c Over a year ago

I had no idea about ThenInclude lol. Thanks for the reply I'll try it out

joseph.c Over a year ago

That fixed my issue, thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related