Where to put my queries - model vs. controller

Question

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.

I keep the data access in a completely separate layer, not in the UI at all. — Forty-Two, Sep 18 '12 at 20:32
@Forty-Two I'm not sure I understand you. My data access is in the controller and the model, not the view. — ashes999, Sep 18 '12 at 20:50
In my asp.net mvc projects, I consider the models, views and controllers to all be part or the presentation layer. I keep data access stuff like domain models, contexts and repositories in other layers: data access layer, business logic layer, etc. — Forty-Two, Sep 18 '12 at 20:53

score 3 · Accepted Answer · edited May 23 '17 at 11:47

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...

I actually realized and started implementing this already. +1 for DI, I need to roll that in. — ashes999, Sep 18 '12 at 23:08

score 0 · Answer 2 · answered Sep 18 '12 at 20:13

0

I can't speak for dapper personally, but I've always restricted my db access to models only except in very rare circumstances. That seems to make the most sense in my opinion.

A little more info: http://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller

A model notifies its associated views and controllers when there has been a change in its state. This notification allows the views to produce updated output, and the controllers to change the available set of commands. A passive implementation of MVC omits these notifications, because the application does not require them or the software platform does not support them.

Basically, data access in models seems to be the standard.

answered Sep 18 '12 at 20:13

Mansfield

14,445
18
76
112

In MVC3, controllers are the glue that usually (in my limited experience) fetch models and feed them to the view. So it makes sense for them to have data access, too. For example, `Project.Details` will need to load the project and give it to the view to display details. – ashes999 Sep 18 '12 at 20:51
@ashes999 Generally, controllers should fetch models that already contain the necessary data. All I do in my controls is instantiate model objects and call functions on them - I don't do any actual data access. – Mansfield Sep 18 '12 at 20:58

score 0 · Answer 3 · answered Sep 18 '12 at 22:34

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).

There are few issues with static repositories: They lead to a strong coupling between client of the repository and the implementation and they make unit testing impossible. — Void Ray, Sep 18 '12 at 23:52

Where to put my queries - model vs. controller

3 Answers3