I’m trying to sort a list in MongoDB using C# with the following method:

public async Task<SearchResponseDTO<CampaignValidateDTO>> Search(SearchRequestDTO<CampaignValidateSearchFilterDTO> searchRequestDTO, string tenantId)
        {
            var filterDefinition = GetSearchFilter(searchRequestDTO, tenantId);

            var totalItems = await mongoCollection.CountDocumentsAsync(filterDefinition);
            var itemsWithLookup = await mongoCollection.Aggregate()
                                    .Match(filterDefinition)
                                    .As<CampaignValidateDTO>()
                                    .Lookup<CampaignValidateDTO, Campaign, CampaignValidateDTO>(campaignCollection,
                                        campaignvalidate => campaignvalidate.CampaignId,
                                        foreignfield => foreignfield.Id,
                                        @as => @as.Campaigns)
                                    .AppendStage<CampaignValidateDTO>("{ $sort: { 'Campaigns.CreatedDate': -1 } }")
                                    .Skip(searchRequestDTO.Pagination.Offset)
                                    .Limit(searchRequestDTO.Pagination.Limit)
                                    .ToListAsync();

            return new SearchResponseDTO<CampaignValidateDTO>
            {
                TotalItems = (int)totalItems,
                Items = itemsWithLookup
            };
        }

However, the sorting does not work as expected. Here is an example of the returned JSON:

{
    "totalItems": 2,
    "items": [
        {
            "id": "ec6cb5bf-e493-4bed-9f3b-295908dc0b2a",
            "campaignId": "7425739c-8f4f-4bd7-8004-d14a47bcc093",
            "campaign": {
                "name": "Campaign A",
                "site": "",
                "periodDate": {
                    "from": "2024-05-20T03:00:00Z",
                    "to": "2024-05-21T03:00:00Z"
                },
                "rule": {
                    "ruleType": 2,
                    "multipleReachValueCondition": {
                        "reachValue": {
                            "value": 98,
                            "type": 0
                        }
                    }
                },
                "rules": [],
                "prize": {
                    "prizeType": 1,
                    "multipleDiscount": {
                        "discount": {
                            "type": 1,
                            "value": 100
                        }
                    }
                },
                "prizes": [],
                "notificationLayoutId": "599ddbed-05ef-4526-918f-4e7bd4bb022c",
                "participantBaseId": "e2cafb8d-4f0b-4706-a3a6-68bb52f0dbce",
                "status": 4,
                "whatsAppType": "default",
                "isApp": false,
                "layoutType": "default",
                "engajamentSelected": 1,
                "createdDate": "2024-05-20T21:21:14.518Z",
                "nextEngajament": 0,
                "isSegmentationCampaign": false,
                "isRanking": false,
                "segmentations": [],
                "campaignDetails": [],
                "notificationDetails": []
            },
            "validations": [],
            "currentStatus": 0
        },
        {
            "id": "132ea76c-2887-4d49-8709-69d7c8bd5a58",
            "campaignId": "e9c28236-ade0-4085-96f9-1fba41b339a0",
            "campaign": {
                "name": "Campaign B",
                "site": "",
                "periodDate": {
                    "from": "2024-05-21T03:00:00Z",
                    "to": "2024-06-21T03:00:00Z"
                },
                "rule": {
                    "ruleType": 0,
                    "reachPurchaseValue": {
                        "value": 1000,
                        "type": 0
                    }
                },
                "rules": [],
                "prize": {
                    "prizeType": 0,
                    "discount": {
                        "type": 1,
                        "value": 10
                    }
                },
                "prizes": [],
                "tearmsOfServiceId": "41e7e855-4cc4-475c-aae3-e9e3b0394b15",
                "notificationLayoutId": "1e19d321-94fc-4e6f-b8d1-b05d5e0d176f",
                "participantBaseId": "91ecf2a2-c8ea-42d0-9246-f84072c6cc3c",
                "status": 1,
                "whatsAppType": "",
                "isApp": false,
                "layoutType": "custom",
                "engajamentSelected": 0,
                "createdDate": "2024-05-21T13:29:50.859Z",
                "nextEngajament": 0,
                "isSegmentationCampaign": false,
                "isRanking": true,
                "segmentations": [],
                "campaignDetails": [],
                "notificationDetails": []
            },
            "validations": [],
            "currentStatus": 0
        }
    ],
    "nextEngajament": 0
}

In this example, you can see that the first record that comes back is from the 20th and not from the 21st.

What I’ve Tried

1. Sorting within the AppendStage with “{ $sort: {
   ‘Campaigns.CreatedDate’: -1 } }”.
2. Ensured the CreatedDate field is correctly populated and used in
   sorting.

Why is the sorting not working as expected, and how can I ensure that the results are correctly sorted by the CreatedDate field in descending order?

Any guidance or examples on how to properly sort the results after performing the lookup would be greatly appreciated.