Group by version

Question

The Setup

public abstract class Entity
{
    public Guid Id { get; set; }//Primary key
    public int Version { get; set; }//Primary key
    public DateTime Created { get; set; }
    public User CreatedBy { get; set; }
    public bool IsDeleted { get; set; }
}

Both Id and Version are the primary key. An entity automatically generates an Id when created and has a default Version of "0". When the entities are updated, another record is added with same Id and with version "1", etc... When an entity is "deleted", another record is added with same Id, version + 1 and with property IsDeleted set to "true".

The Question

In my repository, I want have a method which returns a queryable of

1. Entities grouped by same ID
2. Highest version (=latest version of the entity)
3. IsDeleted = false

So in short: I want to retrieve a list of entities, only with their latest record (when the entity is not set to "IsDeleted")

The Attempt

I tried 2 different ways to solve this. Which one is correct/better here? (or other suggestions?)

Option 1:

public IQueryable<TEntity> Get<TEntity>(int page, int size) where TEntity : Entity
{
    return _context
        .Query<TEntity>()
        .OrderByDescending(x => x.Version)
        .GroupBy(x => x.Id)
        .SelectMany(x => x)
        .Where(x => !x.IsDeleted)
        .Skip(page * size)
        .Take(size);
 }

Option 2:

public IQueryable<TEntity> Get<TEntity>(int page, int size) where TEntity : Entity
{
    return _context
        .Query<TEntity>()
        .GroupBy(d => d.Id)
        .Select(g => g.OrderByDescending(d => d.Version))
        .OrderByDescending(e => e.First().Version)
        .SelectMany(e => e)
        .Where(x => !x.IsDeleted)
        .Skip(page * size)
        .Take(size);
}

Answer 1

Looking at your requirements:

1. Entities grouped by same ID
2. Highest version (=latest version of the entity)
3. not IsDeleted

If seems to me that you want the highest non-deleted version from every entity.

To make it more clearly, I'll talk about the versions of a document. You want the last document version before the document was deleted, or the last one if it hasn't been deleted yet.

My suggestion would be to first remove all deleted document versions. Then group the documents into groups with same Id.

Now every group contains all non-deleted versions of a document). After that you take the document with the highest version number.

var result = entities                     // from the collection of document
    .Where(entity => !entity.IsDeleted)   // keep the non-deleted ones
    .GroupBy(entity => entity.Id)         // group them into groups with same Id

    // now every group contains all non-deleted versions of a document
    // select the newest document,                         // to do this
    .Select(group => group                                 // order all items in a group by
        .OrderByDescending(groupItem => groupItem.Version) // descending Version number
        .FirstOrDefault());                                // and select the first one

There is a GroupBy overload that combines GroupBy and Select:

 // group the remaining non-deleted items into groups with same Id
.GroupBy(entity => entity.Id     
   (id, entitiesWithThisId => entitiesWithThisId.OrderByDescending(entity => entity.Version)
                             // order all entities of a group by descending Version number
        .FirstOrDefault());  // and select the first one

Answer 2

I think your option 2 almost works how you want it to (based on the comments). You just need to remove your .OrderByDescending(e => e.First().Version) and then select e.First() from each group:

public IQueryable<TEntity> Get<TEntity>(int page, int size) where TEntity : Entity
{
    return _context
        .Query<TEntity>()
        .GroupBy(d => d.Id)
        .Select(g => g.OrderByDescending(d => d.Version))
        .Select(e => e.First())
        .Where(x => !x.IsDeleted)
        .Skip(page * size)
        .Take(size);
}

Try it online