title: LINQ/C#: Join, Group and Select Last in each group?
body: <p>Using C# and LINQ, I have a table of employees, only some of whom are active and a table of events.  I need to join the table of events to the employees on ID, group the events by employee and select the newest event. I've tried things along the line of this but getting crosseyed and nowhere.</p>
  
  <pre><code> var emp = (from em in dc.Employees
                     where (em.Active == true)
                     join ev in dc.Events on em.ID equals ev.Employee orderby ev.Time
  </code></pre>
  
  <p>OK, by cannibalizing some older code from another project, I got this:</p>
  
  <pre><code> var attends = dc.Events               
   .GroupBy(c =&gt; c.Employee)
   .Select(x =&gt; x.OrderByDescending(y =&gt; y.Time).FirstOrDefault())
   .Join(dc.Employees,u =&gt; u.Employee,o =&gt; o.ID,
          (u, o) =&gt; new { o.Name,u.Time,u.EventType}).ToList();
  </code></pre>
  
  <p>Which seems to work, but now I need to filter for Employees where active is true... where would I do that?</p>
  
  <p>OK... I think (knock on wood) I got it, in the event it helps someone else, this seems to do it:</p>
  
  <pre><code>var attends = dc.Events                
   .GroupBy(c =&gt; c.Employee)
   .Select(x =&gt; x.OrderByDescending(y =&gt; y.Time).FirstOrDefault())
   .Join(dc.Employees.Where (z=&gt;z.Active), u =&gt; u.Employee,o =&gt; o.ID, 
          (u, o) =&gt; new { o.Name,u.Time,u.EventType}).ToList();
  
  
      }
  </code></pre>
  
  <p>If not, I'll be back crying...</p>