MongoDB C#聚合Lookup后排序失效问题排查与解决

问题描述 #

我在C#中使用MongoDB对列表进行排序时遇到了问题，编写的搜索方法如下：

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
    };
}

返回的JSON结果未按预期排序，5月20日的记录排在了5月21日之前：

{"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}

已尝试的操作：

• 在AppendStage中使用"{ $sort: { 'Campaigns.CreatedDate': -1 } }"进行排序；
• 确认CreatedDate字段已正确赋值。

附DTO定义：
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 => 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 List<CampaignDetailDTO> CampaignDetails { get; set; }
        public List<NotificationDetailDTO> NotificationDetails { get; set; }
    }
}

问题原因 #

排序失效的核心原因是MongoDB字段名大小写不匹配：

• 从返回的JSON可以看到，数据库中存储的日期字段是createdDate（小驼峰，首字母小写）；
• 但排序语句中使用的是Campaigns.CreatedDate（首字母大写），MongoDB对字段名大小写敏感，导致无法识别目标排序字段，排序逻辑未生效。

另外，使用字符串形式的AppendStage容易出现拼写或大小写错误，建议优先使用强类型API避免这类问题。

解决方案 #

方案1：修正排序字段的大小写 #

将AppendStage中的排序字段改为与数据库存储一致的createdDate：

.AppendStage<CampaignValidateDTO>("{ $sort: { 'Campaigns.createdDate': -1 } }")

方案2：使用强类型SortStage（推荐） #

使用MongoDB.Driver的强类型排序API，彻底避免字段名拼写错误：

.SortByDescending(c => c.Campaigns.First().CreatedDate)

修改后的完整聚合代码：

var itemsWithLookup = await mongoCollection.Aggregate()
                        .Match(filterDefinition)
                        .As<CampaignValidateDTO>()
                        .Lookup<CampaignValidateDTO, Campaign, CampaignValidateDTO>(campaignCollection,
                            campaignvalidate => campaignvalidate.CampaignId,
                            foreignfield => foreignfield.Id,
                            @as => @as.Campaigns)
                        .SortByDescending(c => c.Campaigns.First().CreatedDate) // 使用强类型排序
                        .Skip(searchRequestDTO.Pagination.Offset)
                        .Limit(searchRequestDTO.Pagination.Limit)
                        .ToListAsync();

额外说明 #

因为每个CampaignValidate通过CampaignId关联唯一的Campaign，所以Lookup后的Campaigns列表只会有一个元素，使用First()指定排序字段是安全的。

内容的提问来源于stack exchange，提问作者Rafael Augusto