How to get list of latest entry for each parent using entity framework?

Question

I have a table named "Children" which has columns like "Id", "ParentId", "Description", "LastUpdate" etc. I want to query a list which will have distinct rows for each parentId and I want those rows latest according to the value of the "LastUpdate" column which is a DateTime. What is the simplest way to achieve this? I have tried something like this:

var latestList = _context.Children.where(x => !x.Deleted).OrderByDescending(x => x.LastUpdate).DistinctBy(x => x.ParentId).ToList();

But this couldn't be translated into sql. So what else can I do now?

Answer 1

If I understand your query correctly, for each Parent you want the Child that has the latest LastUpdate:

var latestList = _context.Children
    .Where(x => !x.Deleted)
    .GroupBy(x => x.ParentId)
    .Select(g => g.MaxBy(x => x.LastUpdate))
    .ToList();

You can order the children before .ToList();