Json – MongoDB Sorting Not Working as Expected in C# with Aggregate Lookup

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.

UPDATE:

CampaignValidateDTO

using Project.CrossCutting.DTO.Campaign;
using Project.CrossCutting.Enum;
using System.Text.Json.Serialization;

namespace Project.CrossCutting.DTO.CampaignValidate
{
    public class CampaignValidateDTO
    {
        public string Id { get; set; }
        public string Type { get; set; }
        public string CampaignId { get; set; }
        [JsonIgnore]
        public List<CampaignDTO> Campaigns { get; set; }
        public CampaignDTO Campaign { get => Campaigns?.FirstOrDefault(); }
        public List<ValidationFileDTO> Validations { get; set; }
        public CampaignValidationType CurrentStatus { get; set; }

    }
}

CampaignDTO

using Project.CrossCutting.Enum;

namespace Project.CrossCutting.DTO.Campaign
{
    public class CampaignDTO
    {
        public string Name { get; set; }
        public string Site { get; set; }
        public AvailabilityDateDTO PeriodDate { get; set; }
        public RuleDTO Rule { get; set; }
        public List<RuleDTO> Rules { get; set; }
        public PrizeDTO Prize { get; set; }
        public List<PrizeDTO> Prizes { get; set; }
        public string TearmsOfServiceId { get; set; }
        public string NotificationLayoutId { get; set; }
        public string ParticipantBaseId { get; set; }
        public CampaignStatus? Status { get; set; }
        public string WhatsAppType { get; set; }
        public bool IsApp { get; set; }
        public string LayoutType { get; set; }
        public string ProgramId { get; set; }
        public string ProgramName { get; set; }
        public string Logo { get; set; }
        public string AspectRatio { get; set; }
        public int EngajamentSelected { get; set; }
        public DateTime CreatedDate { get; set; }
        public int NextEngajament { get; set; }
        public string Qtd { get; set; }
        public bool IsSegmentationCampaign { get; set; }
        public bool IsRanking { get; set; }
        public List<string> Segmentations { get; set; }
        public List<string> Imgs { get; set; }
        //public string SegmentationBaseId { get; set; }
        public List<CampaignDetailDTO> CampaignDetails { get; set; }
        public List<NotificationDetailDTO> NotificationDetails { get; set; }
    }


}