MongoDB C#从组获取最新文档 [英] MongoDB C# Get latest document from group
问题描述
我有一组假付款状态，每个状态都有一个付款ID.
I have a group of statuses of pretend payments, each with a payment ID.
我想获取每个付款ID的最新状态.我进行的测试创建了一些虚拟数据，然后尝试对其进行查询.我已经走了这么远:
I want to get the latest status for each payment ID. The test I have creates some dummy data and then tried to query it. I've got this far:
[Test]
public void GetPaymentLatestStatuses()
{
var client = new TestMongoClient();
var database = client.GetDatabase("payments");
var paymentRequestsCollection = database.GetCollection<BsonDocument>("paymentRequests");
var statusesCollection = database.GetCollection<BsonDocument>("statuses");
var payment = new BsonDocument { { "amount", RANDOM.Next(10) } };
paymentRequestsCollection.InsertOne(payment);
var paymentId = payment["_id"];
var receivedStatus = new BsonDocument
{
{ "payment", paymentId },
{ "code", "received" },
{ "date", DateTime.UtcNow }
};
var acceptedStatus = new BsonDocument
{
{ "payment", paymentId },
{ "code", "accepted" },
{ "date", DateTime.UtcNow.AddSeconds(-1) }
};
var completedStatus = new BsonDocument
{
{ "payment", paymentId },
{ "code", "completed" },
{ "date", DateTime.UtcNow.AddSeconds(-2) }
};
statusesCollection.InsertMany(new [] { receivedStatus, acceptedStatus, completedStatus });
var groupByPayments = new BsonDocument { {"_id", "$payment"} };
var statuses = statusesCollection.Aggregate().Group(groupByPayments);
}
但是现在我在砖墙上.
任何朝着正确方向戳戳都会有所帮助.我不确定我是否没有低头看望远镜.
Any poking in the right direction would help. I'm not sure that I'm not looking down the wrong end of the telescope.
以下提供了正确文档的ID.
The following gives me the IDs of the correct documents.
var groupByPayments = new BsonDocument
{
{ "_id", "$payment" },
{ "id", new BsonDocument { { "$first", "$_id" } } }
};
var sort = Builders<BsonDocument>.Sort.Descending(document => document["date"]);
var statuses = statusesCollection.Aggregate().Sort(sort).Group(groupByPayments).ToList();
我是否可以通过一个查询获得全部文档，还是现在必须重新发出命令以获取该列表中的所有文档?
Can I get the full documents with a single query though, or do I have to now re-issue a command to get all the documents in that list?
推荐答案
让我们从获取您想要实现的目标的简单方法开始.在MongoDB的C#驱动程序2.X中，您可以找到AsQueryable扩展方法，该方法使您可以从集合中创建LINQ查询.该Linq提供程序是基于MongoDB的聚合框架构建的，因此最终，您的链接查询将转换为聚合管道.因此，如果您有这样的课程:
Let's start with the easy way to get what you're trying to achieve. In the C# Driver 2.X of MongoDB you can find AsQueryable extension method that let's you create LINQ queries from your collections. This Linq provider was built over the Aggregation framework of MongoDB, so at the end your link query is going to be translated to an aggregation pipeline. So, if you have a class like this:
public class Status
{
public ObjectId _id { get; set; }
public ObjectId payment { get; set; }
public string code { get; set; }
public DateTime date { get; set; }
}
您可以创建如下查询:
var statusesCollection = database.GetCollection<Status>("statuses");
var result= statusesCollection.AsQueryable()
.OrderByDescending(e=>e.date)
.GroupBy(e=>e.payment)
.Select(g=>new Status{_id =g.First()._id,
payment = g.Key,
code=g.First().code,
date=g.First().date
}
)
.ToList();
现在您可能想知道为什么如果我能从每个组中调用First扩展方法得到相同的结果，为什么我必须将结果投影到新的Status类实例中?不幸的是，目前尚不支持.原因之一是因为Linq提供程序正在使用 $ first 操作当它构建聚合管道时，这就是$first操作的工作方式.另外，如您在链接中看到的一样，当您在$group阶段中使用$first时，在中共享时，$group阶段应该紧随$sort阶段以使输入文档具有定义的顺序
Now you may wondering why I had to project the result to a new instance of Status class if I could get the same result calling First extension method from each group? Unfortunately that is not supported yet. One of the reason is because the Linq provider is using $first operation when it build the aggregation pipeline, and that is how $first operation works. Also, as you can see in the link a shared earlier,when you use $first in a $group stage, the $group stage should follow a $sort stage to have the input documents in a defined order.
现在，假设您不想使用Linq，并且想自己创建聚合管道，则可以执行以下操作:
Now, supposing you don't want to use Linq and you want to work creating the aggregation pipeline by yourself, you could do the following:
var groupByPayments = new BsonDocument
{
{ "_id", "$payment" },
{ "statusId", new BsonDocument { { "$first", "$_id" } } },
{ "code", new BsonDocument { { "$first", "$code" } } },
{ "date", new BsonDocument { { "$first", "$date" } } }
};
var sort = Builders<BsonDocument>.Sort.Descending(document => document["date"]);
ProjectionDefinition<BsonDocument> projection = new BsonDocument
{
{"payment", "$_id"},
{"id", "$statusId"},
{"code", "$code"},
{"date", "$date"},
};
var statuses = statusesCollection.Aggregate().Sort(sort).Group(groupByPayments).Project(projection).ToList<BsonDocument>();
此解决方案的优点是您可以一次获取数据，缺点是必须投影所需的所有字段.我的结论是文档没有很多字段或者您没有不需要您文档中的所有字段，我会使用此变体.
The advantage of this solution is that you get the data in one round trip, and the disadvantage is you have to project all the fields that you need.My conclusion would be if the document doesn't have many fields or you don't need all the fields from your document I would use this variant.
这篇关于MongoDB C#从组获取最新文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
