Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Should write complex query in Repository or Service layer?

I are planning migrate our data access layer to using repository pattern and unit of work.

I do know repository will help me to change persistence store (database, collection...etc) and technology such as EF to MongoDB easily. So I noticed some key points of implementation of a repository such as:

  1. Return IEnumerable instead of IQueryable
  2. Repository should take responsibilities for CRUD operations only
  3. Return type of repository method should be model (entity)
  4. Only implement repository for aggregate root

If I apply these key points during implement repository in my project, I totally lost how to deal with complex query in which related to multiple entities.

Currently what I already had was that on BLL library with a lot of services class will contact directly to DbContext and DbSet of EF and some of validation like this:

public IEnumerable<ProjectDTO> GetProjectWithDetails()
{
    // Validation

    // Logging

    // Can be any logic need to before query data.  

    Dbcontext.Projects.Where(p => 
    // multiple of conditions go here follow business rules
    // conditions will need to check another entities (task, phase, employee...) such as:
    // 1. project have task status 'in-progress' .. etc
    // 2. project have employeeid 1,2,3..
    // 3. project have stask start at some specific date.
    // 4....    
    )
    .Select(p => new ProjectDTO
    {
        Label = p.Label,
        Phase = new PhaseDTO{
            Label = p.Phase.Label,
            Tasks = p.Phase.Tasks.Select(t => new TaskDTO{
                // some related properties
            })
        }
    }).ToList();
} 

I am currently using Data Transfer Object (DTO) to be the middle classes between model and viewmodel on controller and using Mapper to map properties.

If I keep key notes on repository above I need to do multiple round trip to database for getting data, and it will return whole model instead of useful columns. But if I migrate those kind of methods to repository I will broken repository pattern because it will contain business logic and return type not an model.

So question is what should I do in this case? Please give me some advise to put me on the right track.

Many thanks.

like image 925
vietvoquoc Avatar asked May 13 '18 04:05

vietvoquoc


People also ask

What is the difference between service layer and repository layer?

Repository layer is implemented to access the database and helps to extend the CRUD operations on the database. Whereas a service layer consists of the business logic of the application and may use the repository layer to implement certain logic involving the database.

What is a complex query in a database?

Complex SQL is the use of SQL queries which go beyond the standard SQL of using the SELECT and WHERE commands. Complex SQL often involves using complex joins and sub-queries, where queries are nested in WHERE clauses. Complex queries frequently involve heavy use of AND and OR clauses.

What is the purpose of repository layer?

Repositories are classes or components that encapsulate the logic required to access data sources. They centralize common data access functionality, providing better maintainability and decoupling the infrastructure or technology used to access databases from the domain model layer.

Can the customerservice connect to both repositories and query objects?

The CustomerService (BLL) can connect to both the repositories or the query objects, depending on what it needs. The service can in principle mix and match to its liking, though usually you tend to create query objects that precisely match a specific CustomerService method.

Why use repository interfaces instead of a service layer?

It took me a little while to really grasp this. The reason is that if a controller needs some data from the database, it can simply use the repository interfaces to get the data. There is no need to go through a service layer for getting data.

Is a repository method an abstraction of a query?

In the previous chapter we said that a repository method should be an abstraction of a database query and at this point, it feels like we are writing the same query over and over again just so we can match the object types. Like they say in the cheesy commercials – “Fortunately you have come to the right place!”.

What happens when your repository methods return IQueryable?

When your repository methods return IQueryable, someone else is going to get that IQueryable and compose a query on top of it. Here’s the result: Can you see the difference between these two code snippets?


1 Answers

This depends on opinion and the use case, but I personally do not agree with some of the key points you mentioned.

Return IEnumerable instead of IQueryable

Agree. Returning IQueryable defeats basic purpose of existence of Repository. There are lot many articles on net explaining how this creates more problem than a solution. Though, I have learned to never say never. Refer this, this, or this. Or simply search google.

Repository should take responsibilities for CRUD operations only

Agree. With simple CRUD, it may also do complex reads and writes. My experience tell that in exceptional cases, you have to put a part of business logic in repository if you want to implement it on RDBMS side. This is not right or wrong. If you know what you are doing, there should not be an issue.

Return type of repository method should be model (entity)

If you are not using DDD, then yes. Otherwise, it is implementation decision. With full ORM like EF or NHibernate, it is better to return Domain Model directly instead of per table Entity instance.

It is always suggested that Repository should return Domain Model. That way, mapping of data returned from RDBMS with the Domain Model (and vice versa) becomes responsibility of repository. This avoids necessity of leaking the persistence concerns outside the repository and thus makes your rest of the application persistence ignorant.

But, not every application implement DDD. Many small applications design entities those are mapped 1 to 1 with their database design. In this case, repository may return entity (which is equivalent to your table and fields) itself and mapping becomes responsibility of calling code. Or, repository may map the necessary model and return the model itself. This is strongly discouraged because the problems stated above. With this, you have to give up some of the features full ORMs provide.

All this depends on what is your problem, what are your design objectives, size of application and other design patterns implemented etc. That is why it becomes design decision.

Only implement repository for aggregate root

Agreed if it is with DDD. If not, multiple choices are available like per table repository. Again, depends on use case.

About complex query

It is not necessary that repositories should only implement simple CRUD methods. It may also return complex object graph. It may do complex querying. That said, with simple methods like Get, GetById etc, it may also consume complex methods like GetTopBrokenVehicles(vehicleType, top). It is absolutely fine if you write separate method for complex query.

Challenge is that, how you accept the necessary parameters. You may accept the parameters inline or build separate simple input parameter class.

Here is sample code for Repository and UoW.

like image 198
Amit Joshi Avatar answered Oct 19 '22 20:10

Amit Joshi