我的数据库中有这个表:
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
我尝试过这个SQL查询,它部分工作,但我需要包括id在内的整行,如果我将id添加到select中,它就会失败。
我需要编写一个实体框架查询,以便结果具有最后添加的表1、表2、主席1、主席2
SELECT [Name],[Version], MAX(AddedDate) as AddedDate
From JsonSchemas
GROUP BY Name, Version
ORDER BY MAX(AddedDate) DESC
您可以尝试以下代码:
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();
您可以根据名称和版本进行分组,并通过在AddedDate上订购获得最新的AddedDate,如下所示:
var lst = dbContext.JsonSchemas.GetAsNoTracking()
.GroupBy(a => new {a.Name, a.Version})
//.ToList()
.Select(g => g.OrderByDescending(t => t.AddedDate).FirstOrDefault())
.ToList();