title: Calculating difference between different columns in different rows
body: <p>I have a table that records what happens to a vehicle during a visit. For each visit, there is are multiple rows to denote what has been done. The table looks like this</p>
  
  <pre><code>VisitID | ActionID | StartTime | EndTime
     0    |    0     | 1/1/2013  | 1/2/2013
     1    |    0     | 1/2/2013  | 1/4/2013
     1    |    1     | 1/4/2013  | 1/7/2013
     2    |    0     | 1/4/2013  | 1/5/2013
     2    |    1     | 1/5/2013  | 1/6/2013
     2    |    2     | 1/6/2013  | 1/7/2013
  </code></pre>
  
  <p>I wish to construct a LINQ query capable of getting the amount of time a visit took. <code>TotalTime</code> is calculated by first finding the first and last (lowest and highest) <code>ActionID</code>, then <code>last.EndTime - first.StartTime</code>. Expected results:</p>
  
  <pre><code>VisitID | TotalTime
     0    |   1
     1    |   5
     2    |   3
  </code></pre>
  
  <p>I can generate my expected results by doing</p>
  
  <pre><code>var first = db.Visits.Where(v =&gt; v.ActionID == 0)
  var last = db.Visits.GroupBy(x =&gt; x.VisitID).Select(g =&gt; g.OrderByDescending(x =&gt; x.ActionID).First())
  
  first.Join(last, f =&gt; f.VisitID, l =&gt; l.VisitID, (f, l) new{ VisitID = Key, TotalTime = l.EndTime - f.StartTime});
  </code></pre>
  
  <p>I really don't like the hack I used to get the last ActionID, and I would really like to be able to do this within 1 LINQ statement. What do I need to do to achieve this?</p>