ASP.NET MVC - Where to put database queries [closed]

Question

I am trying to learn ASP.NET MVC 4, and I am confused as to where I should put my database queries. I have background in PHP, particularly CodeIgniter, where I am used to putting all db queries in the model. I have this query:

    db.Orders.Where(order => order.columnName == columnName).SingleOrDefault();

Based on this ASP.NET tutorial, I should put it in the controller. However, I found this StackOverflow question, which says that I should put it in the model (similar to what I used to do in PHP). Another answer to that same question mentioned about creating a repository pattern/class that contains all queries.

So my question is, what are the advantages and disadvantages of the following options in terms of code maintenance (readability, effect of changes, etc)?

1. Queries in controllers
2. Queries in models
3. Queries in a separate class/layer

Answer 1

The simple way to handle this with a repository pattern. This is not the best way to do it. But will give you an idea how you can handle this with the repository pattern.

create a repository to do all your db transactions

public interface IRepository
{
  Order GetOrder(int orderId);
}
public class Repository : IRepository
{
   YourDBContext db;
   public Repository()
   {
      db = new YourDBContext ();
   }
   public User GetOrder(int orderId)
   {
      return db.Orders.FirstOrDefault(s=>s.OrderID==orderId);
   }
}

You may create this in the same project (under a "Data access logic") or create a separate class library for this (and refer it to wherever you use it).

And now in your controller, after importing the necessary namespaces, Just create an object of your repository and call the method you are interested in

public OrderController :Controller
{
  protected IRepository repo;
  public OrderController()
  {
    repo=new Repository();
  }
  public OrderController(IRepository repositary)
  {
    // This constructor is for your Unit test project, 
    // you can pass a mock repository here
    // Read dependency injection
    repo=repository;
  }
  public ActionResult details(int id)
  {
    var order=repo.GetOrder(id);
    if(order!=null)
    {
      return View(order);
    }
  }
}

You may consider using a view-model if think your view need it. in that case you need to read the property values from your domain object and set it the the instance of your view-model and return that to your view.

You may move the code to different classes/ layers/projects as your code/functionality grows.