title: Optimize entity framework query
body: <p>I'm trying to make a stackoverflow clone in my own time to learn EF6 and MVC5, i'm currently using OWin for authentication.</p>
  
  <p>Everything works fine when i have like 50-60 questions, i used <a href="http://www.red-gate.com/products/sql-development/sql-data-generator/" rel="noreferrer">Red Gate data generator</a> and try to ramp it up to 1million questions with a couple of thousands of child table rows without relationship just to 'stress' the ORM a bit. Here's how the linq looks like</p>
  
  <pre><code>var query = ctx.Questions
                 .AsNoTracking()     //read-only performance boost.. http://visualstudiomagazine.com/articles/2010/06/24/five-tips-linq-to-sql.aspx
                 .Include("Attachments")                                
                 .Include("Location")
                 .Include("CreatedBy") //IdentityUser
                 .Include("Tags")
                 .Include("Upvotes")
                 .Include("Upvotes.CreatedBy")
                 .Include("Downvotes")
                 .Include("Downvotes.CreatedBy")
                 .AsQueryable();
  
  if (string.IsNullOrEmpty(sort)) //default
  {
      query = query.OrderByDescending(x =&gt; x.CreatedDate);
  }
  else
  {
      sort = sort.ToLower();
      if (sort == "latest")
      {
          query = query.OrderByDescending(x =&gt; x.CreatedDate);
      }
      else if (sort == "popular")
      {
          //most viewed
          query = query.OrderByDescending(x =&gt; x.ViewCount);
      }
  }
  
  var complaints = query.Skip(skipCount)
                        .Take(pageSize)
                        .ToList(); //makes an evaluation..
  </code></pre>
  
  <p>Needless to say i'm getting SQL timeouts and after installing <a href="http://miniprofiler.com/" rel="noreferrer">Miniprofiler</a>, and look at the sql statement generated, it's a monstrous few hundred lines long. </p>
  
  <p>I know i'm joining/including too many tables, but how many projects in real life, we only have to join 1 or 2 tables? There might be situations where we have to do this many joins with multi-million rows, is going stored procedures the only way? </p>
  
  <p>If that's the case, would EF itself be only suitable for small scale projects?</p>