Returning Most Recent Records Based on Serial Number-CodePudding

I have the following model

public class EngineComplianceMetric
{
    public int Id { get; set; }
    public DateTime BuildDate { get; set; }
    public string SerialNumber { get; set; }
    public bool Complied { get; set; }
}

SerialNumber isn't unique to the table, as the same item could have been repaired multiple times over the years.

I'm trying to return a list that only includes each Serial Numbers most recent record that's before some set date.

So far I have

previousCompliedList = _dbContext.ComplianceMetrics
                                 .Where(e => e.BuildDate < startDate)
                                 .Distinct().ToList();

But this is just returning everything prior to the startDate.

CodePudding user response：

You have to group your items by serial numbers, then you have to take the most recent item in each group (by ordering them and taking the first).

previousCompliedList = _dbContext.ComplianceMetrics
      .Where(cm => cm.BuildDate < startDate).ToList()
      .GroupBy(cm => cm.SerialNumber)
      .Select(cm => cm.OrderByDescending(c => c.BuildDate).First());

Based on your error, you have to add the .ToList() after the where (before the GroupBy). But doing this will fetch all the items from the DB where the date is smaller and then run additional manipulations on it in memory.

If you don't want to fetch all the items from the DB, you will have to implement a solution similar to Astrid's suggestion in the comments.

I would recommend googling your error messages: https://stackoverflow.com/a/60874316/7175057