title: Entity Framework - select group by, select max date
body: <p>I have a NoteBrief    </p>
  
  <pre><code>public int Id { get; set; } 
  public string Title { get; set; } 
  public DateTime Created { get; set; }
  public int ParentNoteId { get; set; }
  </code></pre>
  
  <p>Data looks something like</p>
  
  <pre><code>1 Title1 03/31/1987 1
  2 Title1 03/31/1988 1
  3 Title3 01/01/2000 3
  4 Title4 01/01/2001 4
  5 Title4 01/01/2005 4
  </code></pre>
  
  <p>I want to do:</p>
  
  <pre><code>SELECT t1.*
  FROM Notes AS t1 LEFT JOIN Notes AS t2
  ON (t1.ParentNoteId = t2.ParentNoteId AND t1.Created &lt; t2.Created)
  WHERE t2.Created IS NULL;
  </code></pre>
  
  <p>Right now i have:</p>
  
  <pre><code> public IQueryable&lt;NoteBrief&gt; GetNotes()
      {
  
          return _ctx.Notes.Select(r =&gt; new NoteBrief
          {
              Id = r.Id,
              Title = r.Title,
              Created = r.Created,
              ParentNoteId = r.ParentNoteId,
          });
  
      }
  </code></pre>
  
  <p>I'm happy with this, but really don't need the older revisions of a parentNoteId, just need the one that was created last so i can link to it.</p>
  
  <p>I've read many examples, some of which use FirstOrDefault and some that use max. Everytime i try to implement an example though, it doesn't work for me.</p>