How to get a distinct list in Entity Linq to SQL

Tuesday, October 25, 2016 by Nate Bross

Someone asked on Stack Overflow:

I have a generic object table where each entry has a Key and version. So you might have

Object 1, version 1  
Object 1, version 2  
Object 1, version 3  
Object 2, version 1  
Object 2, version 2

How can I request to return the highest version of each object, ie return version 3 of object 1 and version 2 of object 2? I can’t work out how to use a Distinct query as the objects do not match, they have different versions, I want the highest version number of each object

This is my query so far

 var dbObjects = _objectStoreRepository.GetDataObjects().OrderByDescending(d => d.Version).Where(d =>
                    d.ObjectKey.ToLower().Contains(objectKey) &&
                    ((d.Status.ToLower() == request.Status.ToLower()) || string.IsNullOrEmpty(request.Status)) &&
                    d.CreatedAt <= request.AsAtTime).ToList();

But this returns all versions of each matching object, not just the highest version

I posted the following answer, which was chosen as the accepted answer:

You will want to use a .GroupBy with a Select:

var results = _objectStoreRepository
    .GetDataObjects()
    .Where(d 
        => d.ObjectKey.ToLower().Contains(objectKey) 
        && ((d.Status.ToLower() == request.Status.ToLower()) || string.IsNullOrEmpty(request.Status)) 
        && d.CreatedAt <= request.AsAtTime)
    .GroupBy(o => o.ObjectKey)
    .Select(g => g.OrderBy(x => x.Version).LastOrDefault())
    .ToList();

First you group the results by common “key” then once those are grouped we order the results inside that group by the version and take the last or default. You may need to change the Select/OrderBy to fit your exact data.

Originally posted on Stack Overflow — 0 upvotes (accepted answer). Licensed under CC BY-SA.