LINQ query, retrieving the last x distinct data from the rows in tables-CodePudding

here is my question, thanks in advance for your answers. I have three tables, Repair, Status, and the many to many table RepairStatus. Table RepairStatus consists of 4 properties:

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; }
}

Below are sample data and desired result. Suppose I have 7 rows in my RepairStatus table:

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

Now I want to retrieve the last x rows (x marks the number of rows with different distinct RepairId, no duplicates). In the aforementioned example, the expected output would be:

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

Normally I would create another property of type bool and then this filtering would not be a problem, but this is project that I inherited...

CodePudding user response：

Use following :

            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();

CodePudding user response：

Did it finally, the query should go like this:

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