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?
CodePudding user response:
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();