Repository and query objects pattern. How to implement complex queries

c# design-patterns entity-framework sql


I have read a lot of posts of the repository pattern but there are a few practical problems that they doesn't seem to solve or explain. This is what I understand about this two patterns:

The repository and the query pattern are complementary: Query objects represents business logic (WHERE clausules) and the repository pattern has a Get(IPredicate) method that takes a query object and returns a SELECT WHERE result

The repository should not have business logic: All the business logic must go on the query objects

Currently I have a class that wraps each logical object (wich almost always is a single entity object) that implements multiple "Get" methods that implement most complex queries (joins, groupBy, etc...), this isn't a good pattern because classes tend to grow a lot because of boilerplate code for similar queries and its public methods are dependent on context that this class will be used, thus, making this classes unusable for multiple projects that depends on the same database, wich is my primary goal for this project refactoring.

How queries that are more complex than a single SELECT WHERE are implemented with this two patterns without leaking business logic into the repository?

Or if this business logic doesn't fit into the repository nor the query objects where does this logic fit?


3/17/2015 12:13:51 AM

Accepted Answer

There are quite a few implementations of repository patterns and unit of work flying around on the internet. Some of them are quite simple where developer basically implements his own for each table himself manually, some are generic but not advanced, and some are really cool, generic and still offer you the ability to do a decent where, projection and the like.

An example of an in my opinion good implementation can be found here :

It is targetting MVC, which is shown by the interface. I am focussing on WPF applications so I needed to tune it a bit. But the ideas of this unit of work implementation are pretty good.

There is down side to this implementation. Because it is relying on some advanced LINQ and EF functionality one could argue that your underlying access layer is infecting the repository layer and the layers using the repositories.

The point being that when for instance you want to move away from EF, chances are that you would have to change the interface of your repositories.

To show the power of this library some code snippets to prove this :

_fotoRepository = unitOfWork.RepositoryAsync<Foto>();
var fotos = await _fotoRepository
            .Query(r => r.BestelBonId == bestelBonId || werkstukids.Contains(r.WerkstukMetBewerkingenId.Value))

or using projection:

IRepository<Relatie> relatieRepository = unitOfWork.RepositoryAsync<Relatie>();
        var relatiesOverviewsEnumerable = relatieRepository
            .OrderBy(q => q.OrderBy(d => d.RelatieId))
            .Select(b => new RelatieOverview
                RelatieId = b.RelatieId,
                Naam = b.Naam,
                BTW = b.BTW,
                HoofdAdres = b.Adressen.FirstOrDefault(a => a.AdresTypeId == HoofdadresType)
        _relatieOverviews = new ObservableCollection<RelatieOverview>(relatiesOverviewsEnumerable);
3/17/2015 6:53:26 AM

Popular Answer

The Repository pattern works well for standard CRUD applications. Where you need to implement the classic set of create, read, update and delete operations against a single table in a database. In that case you create a repository for each table and allow the read operation to have extra values so that filtering can be applied.

At the next level up you have the Unit of Work pattern. These are used to span multiple repositories and perform business operations. So for example, you would read values from multiple repositories, perform calculations and then write back changes to multiple repositories. All of that would occur inside a transaction so that you always have a consistent state in the database.

The problem is when you have complex queries that span multiple tables. In that case you would place the query into the repository that is the first table in the query from clause. Then you would need to provide parameters to that repository method so it can be parameterised as needed.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow