如何使用BsonDocument C# .NET驱动程序在MongoDB中执行聚合的多个投影和查找？
如何使用BsonDocument C# .NET驱动程序在MongoDB中执行聚合的多个投影和查找？
提问于 2019-09-05 15:35:25
我无法使用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示例中一样，但我总是获得集合的第一个元素。
回答 1
Stack Overflow用户
发布于 2019-09-06 11: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; }
}我花了一段时间，但我最终在这里解决了我的挑战，部分原因是使用ObjectId和string字段执行查找，应用从ObjectId到string的强制转换，并在同一查询中应用多个投影和多次查找。
我希望这能帮助所有那些在理解如何使用BsonDocuments使用MongoDB聚合和C# .NET驱动的人，因为由于ObjectId到字符串的转换，这个查询不可能使用流畅的表示法创建，并将那个用于lookupOne，也许我在最后这部分是错误的，所以如果你知道如何做，相信我也会很高兴地学习它。
为了得到我的解决方案，我从Mikael Koskinen找到了this awesome post，并阅读了mongodb文档以使用expression $toString
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57800546
复制相关文章
相似问题
腾讯云开发者
