问如何使用BsonDocument C# .NET驱动程序在MongoDB中执行聚合的多个投影和查找？
EN

Stack Overflow用户

提问于 2019-09-05 07:35:25

回答 1查看 806关注 0票数 1

我无法使用C# .NET驱动程序的聚合功能来执行多个投影和查找。

这里我需要用mongodb C# .NET驱动程序来复制查询。

db.organizations.aggregate([
  {
    "$project": {
      "_id": {
        "$toString": "$_id"
      },
      "Name": "$Name",
      "LastDateUploaded": "$LastDateUploaded",
      "LastDateEvaluated": "$LastDateEvaluated",
      "PriorityId": "$PriorityId"
    }
  },
  {
    "$lookup": {
      "from": "workflows",
      "localField": "_id",
      "foreignField": "Data.OrganizationId",
      "as": "RelatedWorkflows"
    }
  },
  {
    "$lookup": {
      "from": "priorities",
      "localField": "PriorityId",
      "foreignField": "_id",
      "as": "Priority"
    }
  },
  {
    "$unwind": "$Priority"
  },
  {
    "$project": {
      "Name": "$Name",
      "WorkflowCounter": {
        "$size": "$RelatedWorkflows"
      },
      "LastDateUploaded": "$LastDateUploaded",
      "LastDateEvaluated": "$LastDateEvaluated",
      "Priority": "$Priority.Value"
    }
  },
  {
    "$sort": {
      "Priority": -1,
      "WorkflowCounter": 1,
      "LastDateUploaded": -1,
      "LastDateEvaluated": -1
    }
  }
])

我试着这样做：

public class Organization
    {
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id {get; set;}
        public string NPOId { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        [BsonRepresentation(BsonType.ObjectId)]
        public string PriorityId { get; set; }
        [BsonIgnoreIfDefault]
        public Priority Priority { get; set; }
        public string City { get; set; }
        public string County { get; set; }
        [MaxLength(2)]
        public string State { get; set; }
        [BsonRepresentation(BsonType.ObjectId)]
        public string CountryId { get; set; }
        [BsonIgnoreIfDefault]
        public Country Country { get; set; }
        [MaxLength(5)]
        public string Zip { get; set; }
        public string TaxCode { get; set; }
        public string ParentTaxCode { get; set; }
        [BsonRepresentation(BsonType.ObjectId)]
        public string CharityTypeId { get; set; }
        [BsonRepresentation(BsonType.ObjectId)]
        public string PreferredExtractorId { get; set; }
        [BsonIgnoreIfDefault]
        public User PreferredExtractor{ get; set; }
        [BsonIgnoreIfDefault]
        public CharityType CharityType { get; set; }
        public string URL { get; set; }
        public decimal Revenue { get; set; }
        public decimal Expenses { get; set; }
        public decimal Assets { get; set; }
        public decimal Excess { get; set; }
        public string IRSType { get; set; }
        public string ICPAScore { get; set; }
        public string ICPBScore { get; set; }
        public string[] DefaultRules { get; set; }
        [BsonIgnore]
        public long NumberOfWorkflows { get; set; }
        public DateTime? LastDateUploaded { get; set; }
        public DateTime? LastDateEvaluated { get; set; }
    }

var organizations = db.GetCollection<Organization>("catalog.organizations");
            var aggregation = organizations.Aggregate().Match(_ => true);

            var projectionOne = new BsonDocument {
                { "$project", new BsonDocument { { "_id", new BsonDocument { { "$toString", "$_id" } } } } },
                { "Name", "$Name"},
                { "LastDateUploaded", "$LastDateUploaded" },
                { "LastDateEvaluated", "$LastDateEvaluated"},
                { "PriorityId", "$PriorityId"}
            };

            aggregation.Project(projectionOne);

            aggregation.AppendStage<Organizations>(new BsonDocument {
                { "$lookup", new BsonDocument { { "from", "wcf.workflows" }, { "localField", "_id" }, { "foreignField", "Data.OrganizationId" }, { "as", "RelatedWorkflows" } } }
            });

            aggregation.AppendStage<Organizations>(new BsonDocument {
                { "$lookup", new BsonDocument { { "from", "catalog.priorities" }, { "localField", "PriorityId" }, { "foreignField", "_id" }, { "as", "Priority" } } }
            });

            aggregation.AppendStage<Organizations>(new BsonDocument {
                { "$unwind", "$Priority" }
            });

            aggregation.AppendStage<Organizations>(new BsonDocument {
                { "$project", new BsonDocument { { "Name", "$Name" }, { "WorkflowCounter", new BsonDocument { { "$size", "$RelatedWorkflows" } } } } },
                { "LastDateUploaded", "$LastDateUploaded" },
                { "LastDateEvaluated", "$LastDateEvaluated"},
                { "PriorityValue", "$Priority.Value"}
            });

            aggregation.AppendStage<Organizations>(new BsonDocument {
                { "$sort", new BsonDocument { { "Priority", 1 }, { "WorkflowCounter", 1 }, { "LastDateUploaded", -1 }, { "LastDateEvaluated", -1} } }
            });

            var organizationsList = await aggregation.ToListAsync();

然而，这并不起作用，例如，我颠倒了排序选项，并且总是返回相同的值。我试图获得mongo查询的字符串表示，但我只得到了

aggregate([{ "$match" : { } }])

并且不附加聚合定义。

我尝试使用fluent符号来执行查找，但我需要使用ObjectId和string字段来执行查找，因此，出于这个原因，我首先在MongoDB端使用第一个投影将ObjectId解析为字符串，似乎在fluent符号上没有等价的方法来将ObjectId转换为字符串，以便执行查找(也称为join)。

下面是查询在mongo shell上正常工作的测试证明：https://mongoplayground.net/p/dKT8uQHjHnd

我希望从生成的列表中获得第一个文档，就像在mongo playground示例中一样，但我总是获得集合的第一个元素。

mongodb-.net-driver

mongodb

aggregation-framework

回答 1

Stack Overflow用户

发布于 2019-09-06 03:52:55

我结束了下一步：

        var organizations = db.GetCollection<Organization>("catalog.organizations");
        var projectionOne = new BsonDocument {
                                                { "$project", new BsonDocument { 
                                                        { "_id", new BsonDocument { { "$toString", "$_id" } } },
                                                        { "Name", "$Name"},
                                                        { "LastDateUploaded", "$LastDateUploaded" },
                                                        { "LastDateEvaluated", "$LastDateEvaluated"},
                                                        { "PriorityId", "$PriorityId"}
                                                    } 
                                                }
        };

        var lookupOne = new BsonDocument {
            { "$lookup", new BsonDocument { { "from", "wfc.workflows" }, { "localField", "_id" }, { "foreignField", "Data.OrganizationId" }, { "as", "RelatedWorkflows" } } }
        };
        var lookupTwo = new BsonDocument {
            { "$lookup", new BsonDocument { { "from", "catalog.priorities" }, { "localField", "PriorityId" }, { "foreignField", "_id" }, { "as", "Priority" } } }
        };
        var unwindTwo = new BsonDocument {
            { "$unwind", "$Priority" }
        };

        var projectionTwo = new BsonDocument {
                                            { "$project", new BsonDocument { 
                                                    { "Name", "$Name" }, 
                                                    { "WorkflowCounter", new BsonDocument { { "$size", "$RelatedWorkflows" } } },
                                                    { "LastDateUploaded", "$LastDateUploaded" },
                                                    { "LastDateEvaluated", "$LastDateEvaluated"},
                                                    { "PriorityValue", "$Priority.Value"}
                                                } 
                                            }
        };

        var sort = new BsonDocument {
            { "$sort", new BsonDocument { { "PriorityValue", -1 }, { "WorkflowCounter", 1 }, { "LastDateUploaded", -1 }, { "LastDateEvaluated", -1} } }
        };

        var aggregation = organizations.Aggregate<OrganizationWithWorkflows>(new [] {projectionOne, lookupOne, lookupTwo, unwindTwo, projectionTwo, sort});

        var organizationsList = await aggregation.ToListAsync();

我创建了这个类来获得最后的投影结果：

 [BsonIgnoreExtraElements]
 public class OrganizationWithWorkflows
 {
      public string Id { get; set; }
      public string Name { get; set; }
      public long WorkflowCounter {get; set;}
      public DateTime? LastDateUploaded { get; set; }
      public DateTime? LastDateEvaluated { get; set; }
      public int PriorityValue { get; set; }
 }