Good day. Im working on a Asp.net core ef web app and currently doing the reports using LINQ but when doing grouping by id it does not work or does not display anything, in the 1st screenshot below is the table when I only write Orderbydescending and the 2nd screenshot is the table when I write Group By.

public async Task<ContentResult> GetReportData() {
 
    var perAnalystReport = await  _context.Tats
        .ProjectTo<PerAnalystReportDto( _mapper.ConfigurationProvider )
        .GroupBy (x => x.CrmId )                                                                                                                         
        .OrderByDescending( x => x.Id )
        .ToListAsync();

     return Content( JsonConvert.SerializeObject( perAnalystReport ), "application/json" );
}

PerAnalystReporDto class

 public string RefNo { get; set; }
    public int CrmId { get; set; }
    public DateTime? ReportDate { get; set; }
    public DateTime? HoReceivedDate { get; set; }
    public string ApprovingAuthorityName { get; set; }
    public string BranchName { get; set; }
    public string AreaName { get; set; }
    public string RegionName { get; set; }
    public string Analyst { get; set; }
    public string AccountName { get; set; }
    public string RequestName { get; set; }
    public string FacilityName { get; set; }
    public string Description { get; set; }
    public string CurrencyName { get; set; }
    public int AmountFrom { get; set; }
    public int AmountTo { get; set; }
    public string Status { get; set; }
    public string NextEntityResponsible {get; set;}
    public string Remarks { get; set; }
    public string Position { get; set; }
    public DateTime StatusEffectiveDate { get; set; }
    public int TatCount { get; set; }

    public int Id {get; set;}

Sample Tat Data

Can Someone help me or teach me what's wrong in my code. Thank you!

Working Code:

public ContentResult GetReportData() {

var perAnalystReport = _context.Tats
    .ProjectTo<PerAnalystReportDto( _mapper.ConfigurationProvider )
    .ToList()
    .GroupBy (x => x.CrmId )                                                                                                                         
    .Select(g => g.OrderByDescending( x => x.Id ).First())
    .ToList();

 return Content( JsonConvert.SerializeObject( perAnalystReport ), "application/json" );

CodePudding user response：

So from my understanding, you want a result set that includes the top Id value for each CrmId. You were on the right track, but you needed to apply the OrderByDescending() withing each group and then pick just the latest (first) from within the ordered group members.

I believe the following should give you what you are looking for:

var perAnalystReport = await _context.Tats
    .ProjectTo<PerAnalystReportDto>(_mapper.ConfigurationProvider)
    .GroupBy(x => x.CrmId )
    .Select(g => g.OrderByDescending(x => x.Id).First())
    .ToListAsync();

Or perhaps with slightly better performance:

var perAnalystReport = await _context.Tats
    .GroupBy(x => x.CrmId )
    .Select(g => g.OrderByDescending(x => x.Id).First())
    .ProjectTo<PerAnalystReportDto>(_mapper.ConfigurationProvider)
    .ToListAsync();

The latter avoids building PerAnalystReportDto objects for records being excluded.

For the limited sample data provided, it should yield just the Id = 2 record for the CrmdId = 1 group.