Entity Framework - select group by, select max date

Question

I have a NoteBrief

public int Id { get; set; } 
public string Title { get; set; } 
public DateTime Created { get; set; }
public int ParentNoteId { get; set; }

Data looks something like

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

I want to do:

SELECT t1.*
FROM Notes AS t1 LEFT JOIN Notes AS t2
ON (t1.ParentNoteId = t2.ParentNoteId AND t1.Created < t2.Created)
WHERE t2.Created IS NULL;

Right now i have:

 public IQueryable<NoteBrief> GetNotes()
    {

        return _ctx.Notes.Select(r => new NoteBrief
        {
            Id = r.Id,
            Title = r.Title,
            Created = r.Created,
            ParentNoteId = r.ParentNoteId,
        });

    }

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.

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.

Solution 1

Entity Framework creating IQuerable of the most recent

This is what finally worked for me:

        return from e in _ctx.Notes
               group e by e.ParentNoteId into g
               select g.OrderByDescending(e => e.Created).FirstOrDefault() into r
               select new NoteBrief
               {
                   Id = r.Id,
                   Title = r.Title,
                   Created = r.Created,
                   ParentNoteId = r.ParentNoteId,
               };

Also edited my original post with correct query i was going for.

Thanks.

Solution 2

Try following

    return _ctx.Notes.Select(r => new NoteBrief
    {
        Id = r.Id,
        Title = r.Title,
        Created = r.Created,
        ParentNoteId = r.ParentNoteId,
    }).OrderBy(x=>x.Created).GroupBy(x=>new {Id=x.Id, Title=x.Title}).Select(x=>x.First()).AsQueryable();

}