MongoDB C# 从组中获取最新文档

问题描述

我有一组假付款状态，每个状态都有一个付款 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);

}

但现在我站在一堵砖墙上.

But now I'm at a brick wall.

任何朝正确方向的探索都会有所帮助.我不确定我是不是看错了望远镜的一端.

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# Driver 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# 从组中获取最新文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！