ASP.NET MVC Design Question - Where to put DB access code?

Question

I've been playing around with ASP.NET MVC for the past few weeks. I've got a simple web application with a form which contains a number of drop down lists.

The items in the drop down lists are stored in a database, and I'm using LINQ to SQL to retrieve them.

My question is - where's the appropriate place to put this code? From what I've read so far, it seems that it's advisible to keep the Controller 'thin', but that's where I currently have this code as it needs to be executed when the page loads.

Where should I be putting DB access code etc.? I've included an excerpt from my controller below.

Thanks.

    public ActionResult Index()
    {
        TranslationRequestModel trm = new TranslationRequestModel();

        // Get the list of supported languages from the DB
        var db = new TransDBDataContext();
        IEnumerable<SelectListItem> languages = db.trans_SupportedLanguages
            .Select(c => new SelectListItem
                {
                    Value = Convert.ToString(c.ID),
                    Text = c.Name.ToString()

                });
        ViewData["SourceLanguages"] = languages;
        ViewData["TargetLanguages"] = languages;
        return View();

Answer 1

Your database access code should be in a repository. Example:

public interface ITranslationRepository
{
    Translation GetTransaltion();
}

and the controller would use this repository:

public class TransaltionController : Controller
{
    private readonly ITranslationRepository _repository;
    public TransaltionController(ITranslationRepository repository)
    {
        _repository = repository;
    }

    public ActionResult Index()
    {
        // query the repository to fetch a model
        Translation translation = _repository.GetTransaltion();

        // use AutoMapper to map between the model and the view model
        TranslationViewModel viewModel = Mapper.Map<Translation, TranslationViewModel>(model);

        // pass the view model to the view
        return View(viewModel);
    }
}

So the basic idea is the following:

1. The controller queries a repository to fetch a model
2. The controller maps this model to a view model (AutoMapper is great for this job)
3. The controller passes the view model to the view
4. The view is strongly typed to the view model and uses it to edit/display

As far as the implementation of this repository is concerned feel free to use any data access technology you like (EF, NHibernate, Linq to XML, WCF calls to remote resources over the internet, ...)

There are the following advantages:

1. The controller logic is completely decoupled from the data access logic
2. Your controllers can be unit tested in isolation
3. Your models are not littered with properties that should belong to the UI layer (such as SelectListItem) and thus are reusable across other types of application than ASP.NET MVC.
4. The view model is a class which is specifically tailored to the needs of the view meaning that it will contain specific formatted properties and the view code will be extremely readable.
5. Your views are strongly typed => no more ViewData and ugly magic strings