LINQ查询，从表中的行检索最后x个不同的数据

Question

这是我的问题，提前感谢您的回答。我有三个表，Repair、Status和多对多RepairStatus表。表RepairStatus由4个属性组成：

public class RepairStatus
{
[Key]
public int RepairStatusId { get; set; }
[Required]
[ForeignKey("Repair")]
public int RepairId { get; set; }
public virtual Repair? Repair { get; set; }
[Required]
[ForeignKey("Status")]
public int StatusId { get; set; }
public virtual Status? Status { get; set; }
[Required]
[DataType(DataType.DateTime)]
public DateTime DateTime { get; set; }
}

以下是示例数据和所需结果。假设我的RepairStatus表中有7行：

RepairStatusId    RepairId    StatusId    DateTime 
1                5          1         2022/05/27 18:20:00
2                5          2         2022/05/28 18:20:00
3                4          3         2022/06/15 18:20:00
4                5          4         2022/06/17 18:20:00
5                5          5         2022/06/18 18:20:00
6                6          3         2022/07/25 18:20:00
7                6          1         2022/08/25 18:20:00

现在我想检索最后的x行(x标记具有不同RepairId的行数，没有重复(。在上述示例中，预期输出为：

RepairStatusId    RepairId    StatusId    DateTime 
3                4          3         2022/06/15 18:20:00
5                5          5         2022/06/18 18:20:00
7                6          1         2022/08/25 18:20:00

通常情况下，我会创建另一个bool类型的属性，然后这个过滤不会有问题，但这是我继承的项目。。。

Answer 1

使用以下内容：

List<RepairStatus> status = new List<RepairStatus>()
{
new RepairStatus() { RepairStatusId = 1, RepairId = 5, StatusId = 1 , DateTime = DateTime.Parse("05/27/2022 18:20:00")},
new RepairStatus() { RepairStatusId = 2, RepairId = 5, StatusId = 2 , DateTime = DateTime.Parse("05/28/2022 18:20:00")},
new RepairStatus() { RepairStatusId = 3, RepairId = 4, StatusId = 3 , DateTime = DateTime.Parse("05/15/2022 18:20:00")},
new RepairStatus() { RepairStatusId = 4, RepairId = 5, StatusId = 4 , DateTime = DateTime.Parse("05/17/2022 18:20:00")},
new RepairStatus() { RepairStatusId = 5, RepairId = 5, StatusId = 5 , DateTime = DateTime.Parse("05/18/2022 18:20:00")},
new RepairStatus() { RepairStatusId = 6, RepairId = 6, StatusId = 3 , DateTime = DateTime.Parse("05/25/2022 18:20:00")},
new RepairStatus() { RepairStatusId = 7, RepairId = 6, StatusId = 1 , DateTime = DateTime.Parse("05/25/2022 18:20:00")},
};
var results = status
.OrderByDescending(x => x.RepairStatusId)
.GroupBy(x => x.RepairId)
.Select(x => x.First())
.OrderBy(x => x.RepairStatusId)
.ToList();

Answer 2

最后完成了，查询应该是这样的：

var results = _db.RepairStatus.
.GroupBy(x => x.RepairId)
.Select(x => x.OrderByDescending(x => 
x.RepairStatusId).FirstOrDefault())
.ToList();