How to Filter and get last entry based on date using C# and Mongo

This is a snippet of the json document which has records of card swipes of multiple students in different departments. Each student will have multiple entries based on when they enter the classrooms. The query needs to get the latest based on a list of student ids and department name.

{ 
[
 {
      "studentid"; "stu-1234",
      "dept" : "geog",
      "teacher_id" : 1, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T10:50:00.5230694Z"
     }
 },

 {
      "studentid"; "stu-1234",
      "dept" : "geog",
      "teacher_id" : 2, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T11:50:00.5230694Z"
     }
 },
 {
      "studentid"; "stu-abc",
      "dept" : "geog",
      "teacher_id" : 11, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T09:15:00.5230694Z"
     }
 },
 {
      "studentid"; "stu-abc",
      "dept" : "geog",
      "teacher_id" : 21, 
      "Carddetails": 
     { 
        "LastSwipeTimestamp": "2021-11-25T11:30:00.5230694Z"
     }
 }
]
}

From the data above the query needs to get studentID: stu-abc and stu-1234 from the geog department based on their last card swiped timestamp. In this case it would be stu-abc: 2021-11-25T11:30:00.5230694Z and stu-1234:2021-11-25T11:50:00.5230694Z respectively.

This is my code so far

string [] students = {'stu-abc', 'stu-1234'}
string dept = "geog";
var filter = Builders<BsonDocument>.Filter.In("studentid", students )
                    & Builders<BsonDocument>.Filter.Eq("dept", dept);

_collections.Find(filter).Sort(Builders<BsonDocument>.Sort.Ascending("{\"LastSwipeTimestamp\":-1}")).FirstOrDefault()

But this only gets me one record stu-1234:2021-11-25T11:50:00.5230694Z

How do I get both?

Solution

From your requirement, I think the Aggregation pipeline is more suitable.

[{
    $match: {
        "studentid": {
            "$in": [
                "stu-abc",
                "stu-1234"
            ]
        },
        "dept": "geog"
    }
}, {
    $sort: {
        "Carddetails.LastSwipeTimestamp": -1
    }
}, {
    $group: {
        "_id": {
            "studentid": "$studentid",
            "dept": "$dept"
        },
        "Carddetails": {
            $first: "$Carddetails"
        }
    }
}, {
    $project: {
        _id: 0,
        "studentid": "$_id.studentid",
        "dept": "$_id.dept",
        "Carddetails": "$Carddetails"
    }
}]

Sample Mongo Playground

Convert MongoDB query to Mongo .NET Driver will be as below:

string[] students = { "stu-abc", "stu-1234" };
string dept = "geog";

var pipeline = new BsonDocument[]
{
    new BsonDocument("$match",
    new BsonDocument
    {
        { "studentid",
            new BsonDocument("$in",
                BsonArray.Create(students))
            },
            { "dept", dept }
        }),
    new BsonDocument("$sort",
    new BsonDocument("Carddetails.LastSwipeTimestamp", -1)),
    new BsonDocument("$group",
        new BsonDocument
        {
            { "_id",
                new BsonDocument
                {
                    { "studentid", "$studentid" },
                    { "dept", "$dept" }
                } 
            },
            { "Carddetails",
                new BsonDocument("$first", "$Carddetails") 
            }
        }
    ),
    new BsonDocument("$project",
        new BsonDocument
        {
            { "_id", 0 },
            { "studentid", "$_id.studentid" },
            { "dept", "$_id.dept" },
            { "Carddetails", "$Carddetails" }
        }
    )
};

var result = collection.Aggregate<BsonDocument>(pipeline)
    .ToList();

Console.WriteLine(result.ToJson());

Output