Getting data from Repository to service layer with DTOS

Question

I'm in the process of learning ASP.NET Repository pattern. I have done some research and could not find the right answer.

I have a Database Entity, Employee, on my DAL, that has a number of columns. I have EmployeeRepository that is responsible of querying data using DBContext.

Now I have EmployeeService on my service layer which needs to return a DTO for the employees, that is, it has only a few fields.(First name, last name, date of birth).

Now, If I get the list of Employees from the EmployeeRepository as IQueryable, and select them as EmployeeDTO on my service layer, I would violate the rule by enabling Service Layer accessing to the database.

If not, then EmployeeRepository will return all the data for each employee to the service layer as a list, so that the service layer selects only a few fields to create EmployeeDTO. But in this approach, I will be loading all the data that I don't need.

I wonder if anyone has a suggestion.

Kind Regards,

Answer 1

Important note

All that I will explain in the following is based on the assumption that you want to learn some skills that are useful when building large, enterprise-scale applications. In these scenarios, maintainability and extensibility are a main priority. Why? Because if you do not pay attentions to them, accidental complexity can easily blow of proportions and turn your codebase into one big bowl of spaghetti code.

Back to the definition

First, let's revisit the definition of the repository pattern. If you read it closely, it states that its purpose is to contain the querying logic, which in your scenario is done using LINQ-to-SQL (if you were using ADO.NET, your querying logic would be the actual SQL you were going to execute).
The idea behind this pattern is that you don't want the consumers of your repository to know or care what type of storage mechanism and querying/manipulation ways you are using.
All they want and should know is that they have an interface, something like this for example:

public interface IEmployeeRepository
{
    IEnumerable<Employee> GetAllEmployees();
    Employee GetEmployee(int id);
}

This means that if you introduce IQueryable anywhere in your public interface, this automatically tells them "Hey, although you're talking to an interface, I'll just let you know that it's actually a database you're actually talking to"... which kinda beats the purpose of interfaces and abstractions in general. In this regard, you were right when saying that the Service layer should not have access to the database.

Second, here's a good article to support my opinion that IQueryable is a leaky abstraction - it creates tight-coupling between your application and the storage mechanism.

Back to the interface I defined above, there is a big problem with it - it is very coarse. As per your example, in most some scenarios you do not want to pull all the data related to an employee, nor all the data about all employees, but different subsets of rows and columns each time. But we've just dumped the flexibility given by IQueryable, what is there left?

Bringing only what you neeed

Now since we've established that IQueryable should not be part of your interface's public API, there is only one (that I know of) alternative left to get what you need: a different method for each different subset of employee data you need to fetch.

For example, the scenario you requested in your question would look lie

public interface IEmployeeRepository
{
    IEnumerable<BasicEmployeeData> GetBasicEmployeesData(IEnumerable<int> ids);
}

public class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class EntityFrameworkEmployeeRepository : IEmployeeRepository
{
    public IEnumerable<BasicEmployeeData> GetBasicEmployeesData(IEnumerable<int> ids)
    {
        using (var context = new EmployeeContext())
        {
            return context.Employees.Where(e => ids.Any(id => id == e)).ToList();
            // I haven't tested if .Any works or whether you should instead do .Join
            // but this is orthogonal to the main question
        }
    }
}

If, in the future you will need to pull say, only the id, first name, last name, monthly pay and performance ratings for a couple of employees, all you'll have to do is create a new DTO (EmployeeEvaluation for example), add an interface method returning an IEnumerable of given DTOs and then implement this new method into your EntityFrameworkRepository.

Furthermore, if somewhere even further into the future, you'll want to pull all the data you've got for some employees, your interface might look something like this:

public interface IEmployeeRepository
{
    IEnumerable<BasicEmployeeData> GetBasicEmployeesData(IEnumerable<int> ids);
    IEnumerable<EmployeeEvaluation> GetEmployeeEvaluations(IEnumerable<int> ids);
    IEnumerable<Employee> GetEmployees(IEnumerable<int> ids);
    IEnumerable<Employee> GetEmployees();
}

---

---

In reply to the comment containing "In my case, I will be building the service layer as well. So, I will be the client of my own repository. So, if that's case, I'm not sure if I should repository pattern at all."

When using the word "client", I mean any piece of code that is calling your repository, be it your own or someone else's. So it can be your service, your controller, your tests OR if you decide to compile and distribute your code as a DLL, anyone else that decides to use it.

But, regardless of who calls that code, they should be isolated from knowing what goes on behind the scenes. This also applies if you own the entire codebase, as is your case. Think about the following scenario:
Sometime in the future, your company decides to buy some fancy software that will help them do their accounting and ditch the relational database. In order to keep your application running, you will also have to abandon any usage of Entity Framework and instead do web service calls to this software. Now, if as you suggested, you've used EF directly in all your services (since you haven't implemented a repository, this is the only remaining option) there will be a lot of places in your Servuce layer where you will have to go, remove all references to EF and convert it to code that uses the new web service. This is called shotgun surgery and the cause is that you are mixing two things that should be separate: data retrieval and data processing.
Had you gone the repository way, the only thing that you would have had to change would have been the repository (data retrieval), whereas all the service logic (processing) would have remained untouched. The beauty of decoupled code :)

With these in mind, I strongly encourage you to actually implement the repository pattern. It really helps you keep things decoupled and in accordance with the S in SOLID.

Answer 2

When I start to learn I had same kind of confusion.

Now, If I get the list of Employees from the EmployeeRepository as IQueryable, and select them as EmployeeDTO on my service layer, I would violate the rule by enabling Service Layer accessing to the database.

Your understanding is correct. The repository should not expose IQueryable and should return only the data.

If not, then EmployeeRepository will return all the data for each employee to the service layer as a list, so that the service layer selects only a few fields to create EmployeeDTO. But in this approach, I will be loading all the data that I don't need.

This is really based on your need and your application need. There is no strict rule for method declaration and how they should return. From the following, I could understand two things "return all the data for each employee to the service layer".

• Either you return all the data to the service layer regardless of the employee, For ex. Products, and then you are getting necessary product for the employee. If this is your case, then you can restrict using parameter
• or you mean about the object properties that you are returning. That is based on the DTO from repository to Service Layer. You can define your own DTO based on the need. For this, My suggestion would be if you can keep separate object for Db and DTO, then it would complicate your system and maintaining would be difficult. When you expose your data to the user, then keep the separate object which is necessary.

Sample implementation Dotnet-Repository-Pattern-Template-With-Unit-Of-Work

In this example three layers has been used.

1. Api - To expose the data to the user
2. BL - Service Layer - Handle all the business logic and deal with the repository for CRUD
3. Persistence- To deal with the persistence - encapsulate large/complex queries.

Answer 3

I think you should return from your repository only the columns what you need. After that in your service map to the correct Dto. I think this topic has a lot different point of view and is a little hard. In the example bellow there isn't a service, only the repo and the api controller.

    [HttpGet("{id}")]
    public IActionResult GetCity(int id, bool includePointsOfInterest = false)
    {
        var city = _cityInfoRepository.GetCity(id, includePointsOfInterest);

        if (city == null)
        {
            return NotFound();
        }

        if (includePointsOfInterest)
        {
            var cityResult = Mapper.Map<CityDto>(city); 
            return Ok(cityResult);
        }

        var cityWithoutPointsOfInterestResult = Mapper.Map<CityWithoutPointsOfInterestDto>(city);
        return Ok(cityWithoutPointsOfInterestResult);
    }