title: How to get a count of records with entity framework and repository pattern
body: <p>We have developed an application in MVC, using entity framework and the repository pattern. When a user logs in they see a dashboard with several counts of shifts depending on their status. See below:</p>
  
  <p><a href="https://i.stack.imgur.com/O6HvO.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/O6HvO.jpg" alt="enter image description here"></a></p>
  
  <p>In the controller we populate the ViewModel using the following line:</p>
  
  <pre><code>viewModel.shiftsInProgress = _shiftDateService.GetShiftDatesByStatusID(72).Count();
  </code></pre>
  
  <p>This links to the following method:</p>
  
  <pre><code> public IList&lt;ShiftDate&gt; GetShiftDatesByStatusID(int statusID)
      {
          return _UoW.ShiftDates.Get(s =&gt; s.shiftDateStatusID == statusID)
              .OrderByDescending(s =&gt; s.shiftID).ToList();
      }
  </code></pre>
  
  <p>This method is also used to pull out the list of shifts by status in another view.</p>
  
  <p>Our Unit of Work (_UOW) is mapped to a generic repository which contains 2 get methods:</p>
  
  <pre><code> public IList&lt;TEntity&gt; Get(Expression&lt;Func&lt;TEntity, bool&gt;&gt; filter = null,Func&lt;IQueryable&lt;TEntity&gt;, IOrderedQueryable&lt;TEntity&gt;&gt; orderBy = null,string includeProperties = "")
      {
          IQueryable&lt;TEntity&gt; query = dbSet;
  
          if (filter != null)
          {
              query = query.Where(filter);
          }
  
          foreach (var includeProperty in includeProperties.Split
              (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
          {
              query = query.Include(includeProperty);
          }
  
          if (orderBy != null)
          {
              return orderBy(query).ToList();
          }
          else
          {
              return query.ToList();
          }
      }
  
      public IEnumerable&lt;TEntity&gt; GetWithRawSql(string query, params object[] parameters)
      {
          return dbSet.SqlQuery(query, parameters).ToList();
      }
  </code></pre>
  
  <p>I am looking some advice on whether or not this is best practice, the queries on the dashboard where the counts are being pulled out are starting to slow the application down. I'm thinking this is because we are pulling back an ilist for each count on the dashboard and then using the Count() method inside the controller for each.</p>
  
  <p>Should I do the following?</p>
  
  <ol>
  <li><p>Add another method to the generic repository, IEnumerable GetAll(); - then use this to get a count of the records.</p></li>
  <li><p>Use the method 'GetWithRawSql' and then use SQL to get a count.</p></li>
  <li><p>Something else? Like below:</p></li>
  </ol>
  
  <p>Would I be better removing the 'Shifts Completed' count then bring back an IList of all incomplete shifts to the controller. Inside the controller then use linq to query by status and use the count() method. i.e.</p>
  
  <pre><code>ilist&lt;shiftdate&gt; allIncompleteShifts = _shiftService.GetIncompleteShifts.ToList();
  
  ViewModel.InProgress = allIncompleteShifts.Where(s =&gt; s.status ==72).Count();
  ViewModel.Submitted = allIncompleteShifts.Where(s =&gt; s.status ==73).Count();
  </code></pre>
  
  <p>How would this affect performance</p>