Entity Framework query to fetch the latest version from table

Question

I Have this table in my DB:

   Id  Name    Version    AddedDate
   1   Table    1         2020-02-26 15:25:04.5366703
   2   Table    1         2020-02-26 15:25:01.8502928
   3   Table    2         2020-02-26 15:18:09.0415632
   4   Table    2         2020-02-26 15:18:23.6646620
   5   Chair    1         2020-02-26 15:16:25.7518968
   6   Chair    1         2020-02-26 15:18:49.1826797
   7   Chair    2         2020-02-26 15:24:41.0905596
   8   Chair    2         2020-02-26 15:24:37.4333049

I have tried this SQL query and it works partially, but i need the entire row including the id and it fails if i add the Id to the select.

I need to write an entity framework query such that the result has the last added Table 1, Table 2, Chair 1, Chair 2

SELECT [Name],[Version], MAX(AddedDate) as AddedDate
From JsonSchemas
GROUP BY Name, Version
ORDER BY MAX(AddedDate) DESC

What have you tried? StackOverflow is a Q&A for problems people face with their own solutions rather than looking for solutions from other people. One option to start looking into for a problem like this would be Linq's `GroupBy` and `OrderByDescending`. — Steve Py, Feb 26 '20 at 22:08
normally this type of thing is done with 'Rank' https://stackoverflow.com/questions/20953645/implementing-rank-over-sql-clause-in-c-sharp-linq — Seabizkit, Feb 27 '20 at 06:43

sam · Answer 1 · 2020-02-27T17:53:32.763

0

You can group on Name and version and get recent AddedDate by ordering on AddedDate as shown below:

var lst = dbContext.JsonSchemas.GetAsNoTracking()
                .GroupBy(a => new {a.Name, a.Version})
                //.ToList()
                .Select(g => g.OrderByDescending(t => t.AddedDate).FirstOrDefault())
                .ToList();

edited Feb 27 '20 at 17:53

answered Feb 26 '20 at 22:21

sam

1,937
1
8
14

score 0 · Answer 2 · answered Feb 27 '20 at 06:09

You could try this code :

var result = (from item in _dbContext.JsonSchemas.ToList()
                 group item by new { item.Name, item.Version } into r
                 select new
                 {
                     Id = r.Select(q => q.Id).First(),
                     Name = r.Key.Name,
                     Version = r.Key.Version,
                     AddedDate = r.Max(q => q.AddedDate)
                 }).ToList();

Entity Framework query to fetch the latest version from table

2 Answers2