Where to put my queries - model vs. controller

architecture asp.net-mvc-3 dapper


I just switched from ActiveRecord/NHibernate to Dapper. Previously, I had all of my queries in my controllers. However, some properties which were convenient to implement on my models (such as summaries/sums/totals/averages), I could calculate by iterating over instance variables (collections) in my model.

To be specific, my Project has a notion of AppSessions, and I can calculate the total number of sessions, plus the average session length, by iterating over someProject.AppSessions.

Now that I'm in Dapper, this seems confused: my controller methods now make queries to the database via Dapper (which seems okay), but my model class also makes queries to the database via Dapper (which seems strange).

TLDR: Should the DB access go in my model, or controller, or both? It seems that both is not correct, and I would like to limit it to one "layer" so that changing DB access style later doesn't impact too much.

Accepted Answer

You should consider using a repository pattern:

With repositories, all of the database queries are encapsulated within a repository which is exposed through public interface, for example:

public interface IGenericRepository<T> where T : class
    T Get(object id);
    IQueryable<T> GetAll();
    void Insert(T entity);
    void Delete(T entity);
    void Save(T entity);

Then you can inject a repository into a controller:

public class MyController
    private readonly IGenericRepository<Foo> _fooRepository;
    public MyController(IGenericRepository<Foo> fooRepository)
        _fooRepository = fooRepository;

This keeps UI free of any DB dependencies and makes testing easier; from unit tests you can inject any mock that implements IRepository. This also allows the repository to implement and switch between technologies like Dapper or Entity Framework without any client changes and at any time.

The above example used a generic repository, but you don't have to; you can create a separate interface for each repository, e.g. IFooRepository.

There are many examples and many variations of how repository pattern can be implemented, so google some more to understand it. Here is one of my favorite articles re. layered architectures.

Another note: For small projects, it should be OK to put queries directly into controllers...

Popular Answer

I agree with @void-ray regarding the repository model. However, if you don't want to get into interfaces and dependency injection you could still separate out your data access layer and use static methods to return data from Dapper.

When I am using Dapper I typically have a Repository library that returns very small objects or lists that can then be mapped into a ViewModel and passed to the View (the mapping is done by StructureMap, but could be handled in the controller or another helper).

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why