title: Accessing Database Entities from Controller
body: <h3>tl;dr</h3>
  
  <p>In a good design. Should accessing the database be handled in a separate business logic layer (in an asp.net MVC model), or is it OK to pass <code>IQueryable</code>s or <code>DbContext</code> objects to a controller? </p>
  
  <p>Why? What are the pros and cons of each?</p>
  
  <hr>
  
  <p>I'm building an ASP.NET MVC application in C#. It uses <a href="https://stackoverflow.com/tags/entity-framework/info">EntityFramework</a> as an ORM.</p>
  
  <p>Let's simplify this scenario a bit.</p>
  
  <p>I have a database table with cute fluffy kittens. Each kitten has a kitten image link, kitten fluffiness index, kitten name and kitten id. These map to an EF generated POCO called <code>Kitten</code>. I might use this class in other projects and not just the asp.net MVC project.</p>
  
  <p>I have a <code>KittenController</code> which should fetch the latest fluffy kittens at <code>/Kittens</code>. It may contain some logic selecting the kitten, but not too much logic. I've been arguing with a friend about how to implement this, I won't disclose sides :)</p>
  
  <h3>Option 1: db in the controller:</h3>
  
  <pre><code>public ActionResult Kittens() // some parameters might be here
  {
     using(var db = new KittenEntities()){ // db can also be injected,
         var result = db.Kittens // this explicit query is here
                        .Where(kitten=&gt;kitten.fluffiness &gt; 10) 
                        .Select(kitten=&gt;new {
                              Name=kitten.name,
                              Url=kitten.imageUrl
                        }).Take(10); 
         return Json(result,JsonRequestBehavior.AllowGet);
     }
  }
  </code></pre>
  
  <h3>Option 2: Separate model</h3>
  
  <pre><code>public class Kitten{
     public string Name {get; set; }
     public string Url {get; set; }
     private Kitten(){
          _fluffiness = fluffinessIndex;
     }
  
     public static IEnumerable&lt;Kitten&gt; GetLatestKittens(int fluffinessIndex=10){ 
          using(var db = new KittenEntities()){ //connection can also be injected
              return db.Kittens.Where(kitten=&gt;kitten.fluffiness &gt; 10)
                       .Select(entity=&gt;new Kitten(entity.name,entity.imageUrl))
                       .Take(10).ToList();
          }
      } // it's static for simplicity here, in fact it's probably also an object method
        // Also, in practice it might be a service in a services directory creating the
        // Objects and fetching them from the DB, and just the kitten MVC _type_ here
  
  }
  </code></pre>
  
  <p>
  
  <pre><code>//----Then the controller:
  public ActionResult Kittens() // some parameters might be here
  {
      return Json(Kittens.GetLatestKittens(10),JsonRequestBehavior.AllowGet);
  }
  </code></pre>
  
  <p>Notes: <code>GetLatestKittens</code> is unlikely to be used elsewhere in the code but it might. It's possible to use the constructor of <code>Kitten</code> instead of a static building method and changing the class for Kittens. Basically it's supposed to be a layer <em>above</em> the database entities so the controller does not have to be aware of the actual database, the mapper, or entity framework. </p>
  
  <ul>
  <li><strong>What are some pros and cons for each design?</strong> </li>
  <li><strong>Is there a clear winner? Why?</strong></li>
  </ul>
  
  <p>Note: Of course, alternative approaches are <em>very</em> valued as answers too.</p>
  
  <p><strong>Clarification 1:</strong> This is <em>not</em> a trivial application in practice. This is an application with tens of controllers and thousands of lines of code, and the entities are not only used here but in tens of other C# projects. The example here is a <em>reduced test case</em>.</p>