How to write this MongoDB (Aggregate) query into C#

Question

I need to perform group-by with the $max operator in MongoDB. I figure out the query which is working in the MongoDB database but was not able to write the same using C#.

db.getCollection('Employee').aggregate(
[  
    {$unwind : "$Projects"},
    {
        "$group" : {
            "_id" : "$EmpId",
            "LastUpdated" : {"$max" : "$Projects.LastUpdated"}
        }
    }
]);

Below C# code is giving an error:

"Projects" is not valid property.

_db.GetCollection<BsonDocument>(collection).Aggregate().Unwind(i=>i.Projects)

Answer 1

Assume this is your sample data:

[{
  "EmpId": 1,
  "Projects": [
    {
      "LastUpdated": {
        "$date": "2021-10-22T16:00:00Z"
      }
    },
    {
      "LastUpdated": {
        "$date": "2021-11-07T16:00:00Z"
      }
    },
    {
      "LastUpdated": {
        "$date": "2022-01-22T16:00:00Z"
      }
    }
  ]
}]

and this is your model class:

public class Employee
{
    public int EmpId { get; set; }
    public List<EmployeeProject> Projects { get; set; }
}

public class EmployeeProject
{
    public DateTime UpdatedDate { get; set; }
}

---

To use the Projects property, you need to specify your collection as Project type as:

_db.GetCollection<Employee>("Employee")

Solution 1: Mix use of AggregateFluent and BsonDocument

var result = _db.GetCollection<Employee>("Employee")
                .Aggregate()
                .Unwind(i => i.Projects)
                .Group(new BsonDocument
                {
                    { "_id", "$EmpId" },
                    { "LastUpdated", new BsonDocument("$max", "$Projects.LastUpdated") }
                })
                .ToList();

---

Solution 2: Full use of AggregateFluent

Pre-requisites:

• Need to create a model for unwinded Project.

public class UnwindEmployeeProject
{
    public int EmpId { get; set; }
    public EmployeeProject Projects { get; set; }
}

var result = _db.GetCollection<Employee>("Employee")
               .Aggregate()
               .Unwind<Employee, UnwindEmployeeProject>(i => i.Projects)
               .Group(
                   k => k.EmpId,
                   g => new
                   {
                       EmpId = g.Key,
                       LastUpdated = g.Max(x => x.Projects.LastUpdated)
                   })
               .ToList();

---

Solution 3: Full use of BsonDocument

With Mongo Compass, you can export your query to C#.

PipelineDefinition<Employee, BsonDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$unwind", "$Projects"),
    new BsonDocument("$group",
        new BsonDocument
        {
            { "_id", "$EmpId" },
            { "LastUpdated",
                new BsonDocument("$max", "$Projects.LastUpdated") }
        })
};

var result = _db.GetCollection<Employee>("Employee")
                .Aggregate(pipeline)
                .ToList();  

Output