title: Performance across to 2 tables, getting latest record from 2nd table
body: <p>I am using Code First, Entities, MVC3 + Razor, we have forms which have many steps in them (which also have to be audited)</p>
  
  <p>I have <code>Forms</code>, which have <code>Steps</code>, so the model looks like this.</p>
  
  <pre><code>public class MyForm
  {
      public int FormId { get; set; }
      public Guid UniqueReferenceId { get; set; }
      public DateTime DateCreatedOn { get; set; }
      public string Name { get; set; }
      public string DetailsOfContact { get; set; }
  
      public virtual ICollection&lt;AuditItem&gt; AuditItems { get; set; }
  }
  
  public class AuditItem
  {
      public int AuditItemId { get; set; }
      public int FormId { get; set; }
      public string ActionByUserUid { get; set; }
      public DateTime DateOfAction { get; set; }
      public int Step { get; set; }
  
      public virtual MyForm MyForm { get; set; }
  }
  </code></pre>
  
  <p>The trouble then comes when I want to display a user interface showing summary information from the form along with what the latest step it is at, details the progress of the many forms submitted, a table with which step they are at and a summary.  The performance is bad even with just a handful of records, let alone when we have thousands in it.  I have to query across the tables, with:</p>
  
  <p>I have to get the step from the latest date, rather than just the latest 'step' number, as it's possible for a form to go back to a previous step.</p>
  
  <pre><code>int latestAuditStep = myForm.AuditItems
      .OrderByDescending(ai =&gt; ai.DateOfAction)
      .Select(ai =&gt; ai.Step)
       .First();
  </code></pre>
  
  <p>Obviously this is quite expensive, and I'm looking for a better way to tackle this modelling issue.</p>
  
  <p>Note: I would have ideally liked to look at using WWF, as that seems the best tool to use in conjunction, but I have not had enough time up front to learn that, rather than just having to 'get the job done', so don't know whether it would have helped or not.</p>