C# Aggregation Group By string and select last by date

Hi,

I’m using the C# Mongo driver and a strongly typed Collection. The collection is based on:

 public class StreetSummary
 {
		[BsonId]
		public string Id { get; set; }

		[BsonElement("streetId")]
		public string StreetId { get; set; }

		[BsonElement("summaryDateUtc)]
		public DateTime SummaryDate { get; set; }

		[BsonElement("field1")]
		public int Field1 { get; set; 

		[BsonElement("field2")]
		public int Field2 { get; set; 
    }

The SummaryDate is holding just the date portion when the summary was calculated. Foreach streetId I want to select the latest StreetSummary document.

I think I need to to group by StreetId but then I am having difficulties selecting the latest record by date. I thought something like below would work saying OrderByDescending isnt supported in expression tree

The method OrderByDescending is not supported in the expression tree: {document}.Or

var collection = db.GetCollection(“streetSummary”);

var result = collection
  .Aggregate()
  .Group(
    x => x.StreetId,
    g => new 
	{
	  StreetId = g.Key,
	  Latest = g.OrderByDescending(m => m.SummaryDate).First()
	}
  )
  ToList();

How should I be doing this?

Hi @Tej_Sidhu, and welcome to the forums!

You can utilise aggregation accumulator operator $max for this. For example:

 var docs = collection.Aggregate()
                      .Group(y=>y.StreetId,
                             z => new { 
                                      StreetId = z.Key, 
                                      LatestSummaryDate = z.Max(a => a.SummaryDate)
                             }
                      ).ToList();

Regards,
Wan.