How to retrieve data with relation in Repository Pattern in the right way?

Question

I have relation in my "User" table that related to department, role and team.

The propose is when I query data over repository so I prefer to return "User" model with fill complete object and properties for Department, Role and Team.

My current solution is use service layer to setting the user properties from related repository (UserService.cs).

My project have Service layer(Service) ,Data Layer (Repository) and Domain Model.

please see for example code below:

Models/User.cs

public class User{
    public string username  { get; set; }
    public string firstname  { get; set; }
    public string surname  { get; set; }
    public Int32 DepartmentId  { get; set; }
    public Department Department { get; set; }
    public Int32 RoleId { get; set; }
    public Role Role { get; set; }
    public IList<Team> Teams { get; set; }
 }

Repository/UserRepository.cs

public class UserRepository : IUserRepository{
    private sqlSelectStatement = String.Format(@"SELECT  u.[Id]
                                                        ,[UserName]
                                                        ,[FirstName]
                                                        ,[LastName]
                                                        ,[Email]
                                                        ,[Telephone]
                                                        ,[DepartmentId]
                                                        ,[TeamId]
                                                        ,[RoleId]
                                                        ,[Enabled]
                                                        ,[Created]
                                                        ,[Modified]
                                                        ,[UpdatedBy]
                                                       FROM {0}[User] ", SchemaOwner);

        public IEnumerable<User> GetAll()
        {
            var dbcommand = this.SessionContext.GetSqlCommand(sqlSelectStatement );
            return this.SessionContext.GetList<User>(dbcommand);
        }
}

Services/UserService.cs

public class UserService : IUserService
{
    private IUserRepository userRepository;
    private ITeamRepository teamRepository;
    private IDepartmentRepository departmentRepository;
    private IRoleRepository roleRepository;


    public IEnumerable<User> GetAll()
     { 
         var users = userRepository.GetAll();
         var departments = departmentRepository.GetAll();
         var teamMembers = teamMemberRepository.GetAll();
         var roles= roleRepository.GetAll();

         foreach (User user in users){
              user.Department = departments.Where(department=>department.Id== user.departmentId);
              user.Role = roles.Where(role=>role.Id== user.roleId);
              user.Teams = teams.where(team=>team.UserId == user.Id);
         }

     }
}

Notice that I am not use any "ORM" framework and I have no plan to use any "ORM"

Answer 1

I think you can easily identify a couple of problems with your current approach. - Loading all users has horrible complexity (SELECT N+1, e.g. for every user your issuing another O(n) query giving you O(n^2) performance) - What happens when you save a user object, should the repository save changes to department etc. as well?

The way I would solve this is to model your domain according to the concept of Aggregate Roots and Value Objects, which are both concepts of Domain Driven Design. You will find plenty of resources for this, but the exec summary is that an aggregate root has global identity and "owns" all its transient value objects in its aggregate boundary. You should have one repository per aggregate root.

Try to avoid explicit dependencies between aggregate roots (i.e. if a department has meaning to your application, do not add a Department property to your User object). Aggregates should be self-contained and depend on each other by virtue of Ids. Operations that modify a single aggregate should be encapsulated on one of the Aggregate objects while operations that need to touch multiple aggregate roots (e.g. assigning a user to a new department) should go into a service that is responsible for loading and modifying both aggregates using their repositories.