Entity Framework creating IQuerable of the most recent

Question

Hi

I have an Entity set that has Entities with a compound key containing ID (GUID) and CreatedAt (DateTime). This CreatedAt is when the entity was created. Each record represents each version of each entity such that muliple records can have the same ID.

I want to create an IQuerable-returning method that I can re-use such that it will only return the latest version of the entity requested, but I'm struggling to find the answer (if, indeed, there is an answer).

I know that I can write a query such as

(from e in context.Entities where e.ID = myID orderby e.CreatedAt).FirstOrDefault();

But I want to be able to do this instead:

(from e in context.GetCurrentEntities() where e.ID = myID).FirstOrDefault();

Such that it will only return the latest versions of the entity required.

Is this doable?

Many thanks for your help.

Lee

Answer 1

I think you are looking at a smaller picture. Have you considered implementing repository pattern?LINK otherwise looking at your requirements you will need to add this GetCurrentEntities method to your context class.

Answer 2

How about:

public partial class MyEntities // or whatever you call your ObjectContext
{
    public IQueryable<Entity> GetCurrentEntities()
    {
        return from e in context.Entities 
               group e by e.ID into g
               from ge in g
               orderby ge.CreatedAt desc
               select ge.First();
    }
}

This is off the top of my head, but it should get you started.

Answer 3

If you group by ID, you can select only the most recent from each group using something like this:

public IQueryable<Entity> GetCurrentEntities()
{
    return from e in this.Entities
           group e by e.ID into g
           select g.OrderByDescending(e => e.CreatedAt).First();
}

You don't need FirstOrDefault() because a group won't be created unless there's at least one Entity in the group.