c# - 如何使用 BsonDocument C# .NET 驱动程序在 MongoDB 中作为聚合执行多个投影和查找?
问题描述
我无法通过 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" : { } }])
并且没有附加聚合定义。
我尝试使用流利的表示法来执行查找,但我需要使用 ObjectId 和字符串字段来执行查找,因此我首先在 mongodb 端解析 ObjectId 以使用第一个投影进行字符串处理,似乎没有等价的流利符号将 ObjectId 转换为字符串以执行查找(也称为连接)。
这里是查询在 mongo shell 上正常工作的测试证明: https ://mongoplayground.net/p/dKT8uQHjHnd
我希望从生成的列表中获取第一个文档,就像在 mongo 游乐场示例中一样,但我总是获取集合的第一个元素。
解决方案
我结束了下一个:
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; }
}
我花了一段时间,但我终于在这里解决了我的挑战,部分是使用 ObjectId 和字符串字段执行查找,应用从 ObjectId 到字符串的强制转换,并在同一查询中应用多个投影和多个查找。
请注意,计数器字段是 的结果数组中包含的元素的大小RelatedWorkflows
,但这也可以在 lookupOne 之后应用展开并分组以使用表达式获取计数器值,{ $sum ,1 }
但在我的情况下,它足以使用来自上述数组的 $size 表达式。
我希望这可以帮助所有那些在理解如何使用 MongoDB 聚合和 C# .NET 驱动程序时遇到困难的人,BsonDocuments
因为由于将 ObjectId 转换为字符串并使用它,因此无法使用流利的表示法创建此查询一个用于lookupOne,也许我对最后一部分有误,所以如果你知道怎么做,相信我也会很高兴学习它。
为了得到我的解决方案,我从 Mikael Koskinen 找到了这篇很棒的帖子,并阅读了 mongodb 文档以使用表达式$toString
推荐阅读
- sql-server - 尝试使用 polybase 在 sql server 中创建外部表时出现一般 ODBC 错误
- java - 调用 setPosition 时,精灵如何缓慢移动
- c++ - 如果给定数组有 4 个连续值,则使用函数 (C++) 查找 4
- c# - 非托管内存中的可变字符串可用于托管空间
- flutter - 有什么办法可以重新加载所有 FutureBuilders?
- unix - VirtualBox 从主机启动脚本
- elasticsearch - ElasticSearch 复合查询
- javascript - 在悬停按钮上添加光泽效果的问题
- javascript - 是否可以在 Ghost 中按标题或 html 中的单词过滤帖子?
- micronaut - Micronaut 上的 RSocket:如何托管端点和调用其他端点