Table join using the repository pattern

Question

I am new to repository pattern. If I want to join two tables, where should I implement the logic? I have implemented it as shown below. Is there any better way to achieve the same?

This is my UserRepository class:

public class UserRepository : Repository<UserMaster>, IUserRepository
{
 public UserRepository(DbContext context)
 : base(context)
 {
 }
 public UserMaster GetById(int id)
 {
 return FindBy(x => x.Userid == id).FirstOrDefault();
 }
}

This is the Service layer from which I am implementing data access functions:

 public class UserService : EntityService<UserMaster>, IUserService
{
 IUnitOfWork _unitOfWork;
 IUserRepository _userRepo;
 IRolePrivilegeMapRepository _rolePrevilegeMapRepo;
 IUserRoleMapRepository _userRoleMapRepo;
 public UserService(IUnitOfWork unitOfWork, IUserRepository userRepo, IRolePrivilegeMapRepository rolePrevilegeMapRepo,IUserRoleMapRepository userRoleMapRepo)
 : base(unitOfWork, userRepo)
 {
 _unitOfWork = unitOfWork;
 _userRepo = userRepo;
 _rolePrevilegeMapRepo = rolePrevilegeMapRepo;
 _userRoleMapRepo = userRoleMapRepo;
 }
 public List<int> GetUserPrevileges(string UserName)
 {
 var rolePrevilegeMap = _rolePrevilegeMapRepo.GetAll();
 var userRoleMap = _userRoleMapRepo.GetAll();
 var userMaster = _userRepo.GetAll();
 var Privs = (from rpm in rolePrevilegeMap
 join urm in userRoleMap on rpm.RoleId equals urm.Roleid
 join um in userMaster on urm.Userid equals um.Userid
 where um.Username.Equals(UserName) && rpm.IsDeleted == false && urm.IsDeleted == false
 select rpm.PrivilegedId).Distinct();
 if (Privs.Any())
 {
 return Privs.ToList();
 }
 else
 {
 return null;
 }
 }
}

I have injected IRolePrivilegeMapRepository, IUserRoleMapRepository, and IUserRepository using Autofac to the UserService class for joining the tables.

 var rolePrevilegeMap = _rolePrevilegeMapRepo.GetAll();
 var userRoleMap = _userRoleMapRepo.GetAll();
 var userMaster = _userRepo.GetAll();

The GetAll method is of return type IQueryable. After that, using that result, I have done the join. Is this the right way to approach? Or Is there any better way to implement the same?

Answer 1

I don't do EF, so I am focusing on the code only. But nevertheless maybe this will help you in doing joins with EF: how-to-join-multiple-tables-using-repository-pattern-entity-framework

• Based on the naming guidelines input parameters should be named using camelCase casing. So

  public List<int> GetUserPrevileges(string UserName) 
  

  should be

  public List<int> GetUserPrevileges(string userName)
  

  The same is true local varaiables like Privs but in addition you shouldn't shorten your varaiables names because you are reducing the readability of your code. So better use privileges instead of Privs.

• This if..else

  if (Privs.Any())
  {
   return Privs.ToList();
  }
  else
  {
   return null;
  }
  

  does not add real value. Why do you want to return null? It would be much easier for the calling code if you return an empty List for the case that there aren't any privileges which match the requirement.

  If the IQuerable<T> doesn't contain any items, a call to ToList() will just create an empty list.

  If you use the returned List<> to just iterate over the items, you won't even need a check if it is empty.