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....
CodePudding user response:
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