Wednesday, November 02, 2011

Linq - Select only latest revision

I'm doing a different approach to what I normally do on my new home page. I'm storing all my data with revisions. Update a photo or an article and it is stored in my database with a new revision. A simple entity might look something like this:
int id
int revision
string name
Now, my challenge was to retrieve persons from my database, but only the latest revision. So I wrote myself some Linq that looks a bit like this:
public IEnumerable<Person> GetPersons()
    return  from person in Person.All()
            group person by person.Id
            into personGroup
            from entity in personGroup
            where entity.Revision == personGroup.Max(x => x.Revision)
            select entity;
And it returns a list of unique persons, but only the latest revision of each.