MongoDB C# 从组中获取最新文档
本文关键字：获取 最新 文档 MongoDB | 更新日期: 2023-09-27 18:34:39
我有一组假装付款的状态，每个状态都有一个付款ID。
我想获取每个付款 ID 的最新状态。 我的测试创建了一些虚拟数据，然后尝试查询它。 我已经做到了：
[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);
}
但现在我在一堵砖墙上。
任何朝正确方向戳都会有所帮助。 我不确定我是否在看望远镜的错误一端。
更新
下面给了我正确文档的 ID。
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();
但是，我可以通过单个查询获取完整文档，还是现在必须重新发出命令才能获取该列表中的所有文档？
让我们从获得您想要实现的目标的简单方法开始。在 MongoDB 的 C# Driver 2.X 中，您可以找到AsQueryable扩展方法，该方法允许您从集合创建 LINQ 查询。这个 Linq 提供程序是在 MongoDB 的聚合框架上构建的，因此最后您的链接查询将被转换为聚合管道。所以，如果你有这样的类：
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阶段，以便按定义的顺序输入文档。
现在，假设您不想使用 Linq，并且想要自己创建聚合管道，则可以执行以下操作：
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>();
此解决方案的优点是您可以在一次往返中获取数据，缺点是您必须投影所需的所有字段。我的结论是，如果文档没有很多字段，或者您不需要文档中的所有字段，我会使用此变体。
这就是我实现它的方式。 不过，必须有更好的方法。
[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" },
{ "id", new BsonDocument { { "$first", "$_id" } } }
};
var sort = Builders<BsonDocument>.Sort.Descending(document => document["date"]);
var statuses = statusesCollection.Aggregate().Sort(sort).Group(groupByPayments).ToList();
var statusIds = statuses.Select(x => x["id"]);
var completedStatusDocumentsFilter =
Builders<BsonDocument>.Filter.Where(document => statusIds.Contains(document["_id"]));
var statusDocuments = statusesCollection.Find(completedStatusDocumentsFilter).ToList();
foreach (var status in statusDocuments)
{
Assert.That(status["code"].AsString, Is.EqualTo("completed"));
}
}
不过，必须有更好的方法。
从 2.5.3 开始，您可以访问聚合内的当前组。这让我们可以构建一个泛型访问器，该访问器将通过本机 mongo 查询从分组中检索第一个元素。
首先是用于反序列化的帮助程序类。 KeyValuePair<TKey,TValue>是密封的，所以我们自己滚动。
/// <summary>
/// Mongo-ified version of <see cref="KeyValuePair{TKey, TValue}"/>
/// </summary>
class InternalKeyValuePair<T, TKey>
{
[BsonId]
public TKey Key { get; set; }
public T Value { get; set; }
}
//you may not need this method to be completely generic,
//but have the sortkey be the same helps
interface IDateModified
{
DateTime DateAdded { get; set; }
}
private List<T> GroupFromMongo<T,TKey>(string KeyName) where T : IDateModified
{
//mongo linq driver doesn't support this syntax, so we make our own bsondocument. With blackjack. And Hookers.
BsonDocument groupDoc = MongoDB.Bson.BsonDocument.Parse(@"
{
_id: '$" + KeyName + @"',
Value: { '$first': '$$CURRENT' }
}");
//you could use the same bsondocument parsing trick to get a generic
//sorting key as well as a generic grouping key, or you could use
//expressions and lambdas and make it...perfect.
SortDefinition<T> sort = Builders<T>.Sort.Descending(document => document.DateAdded);
List<BsonDocument> intermediateResult = getCol<T>().Aggregate().Sort(sort).Group(groupDoc).ToList();
InternalResult<T, TKey>[] list = intermediateResult.Select(r => MongoDB.Bson.Serialization.BsonSerializer.Deserialize<InternalResult<T, TKey>>(r)).ToArray();
return list.Select(z => z.Value).ToList();
}
好。。我在 https://stackoverflow.com/a/672212/346272 的帮助下将其通用化
/// <summary>
/// Mongo-ified version of <see cref="KeyValuePair{TKey, TValue}"/>
/// </summary>
class MongoKeyValuePair<T, TKey>
{
[BsonId]
public TKey Key { get; set; }
public T Value { get; set; }
}
private MongoKeyValuePair<T, TKey>[] GroupFromMongo<T, TKey>(Expression<Func<T, TKey>> KeySelector, Expression<Func<T, object>> SortSelector)
{
//mongo linq driver doesn't support this syntax, so we make our own bsondocument. With blackjack. And Hookers.
BsonDocument groupDoc = MongoDB.Bson.BsonDocument.Parse(@"
{
_id: '$" + GetPropertyName(KeySelector) + @"',
Value: { '$first': '$$CURRENT' }
}");
SortDefinition<T> sort = Builders<T>.Sort.Descending(SortSelector);
List<BsonDocument> groupedResult = getCol<T>().Aggregate().Sort(sort).Group(groupDoc).ToList();
MongoKeyValuePair<T, TKey>[] deserializedGroupedResult = groupedResult.Select(r => MongoDB.Bson.Serialization.BsonSerializer.Deserialize<MongoKeyValuePair<T, TKey>>(r)).ToArray();
return deserializedGroupedResult;
}
/* This was my original non-generic method with hardcoded strings, PhonesDocument is an abstract class with many implementations */
public List<T> ListPhoneDocNames<T>() where T : PhonesDocument
{
return GroupFromMongo<T,String>(z=>z.FileName,z=>z.DateAdded).Select(z=>z.Value).ToList();
}
public string GetPropertyName<TSource, TProperty>(Expression<Func<TSource, TProperty>> propertyLambda)
{
Type type = typeof(TSource);
MemberExpression member = propertyLambda.Body as MemberExpression;
if (member == null)
throw new ArgumentException(string.Format(
"Expression '{0}' refers to a method, not a property.",
propertyLambda.ToString()));
PropertyInfo propInfo = member.Member as PropertyInfo;
if (propInfo == null)
throw new ArgumentException(string.Format(
"Expression '{0}' refers to a field, not a property.",
propertyLambda.ToString()));
if (type != propInfo.ReflectedType &&
!type.IsSubclassOf(propInfo.ReflectedType))
throw new ArgumentException(string.Format(
"Expresion '{0}' refers to a property that is not from type {1}.",
propertyLambda.ToString(),
type));
return propInfo.Name;
}
对于奖励积分，您现在可以轻松执行 mongo 的其他任何分组操作，而无需与 linq 助手战斗。有关所有可用的分组操作，请参阅 https://docs.mongodb.com/manual/reference/operator/aggregation/group/。让我们添加一个计数。
class MongoKeyValuePair<T, TKey>
{
[BsonId]
public TKey Key { get; set; }
public T Value { get; set; }
public long Count { get; set; }
}
BsonDocument groupDoc = MongoDB.Bson.BsonDocument.Parse(@"
{
_id: '$" + GetPropertyName(KeySelector) + @"',
Value: { '$first': '$$CURRENT' },
Count: { $sum: 1 }
}");
以与之前完全相同的方式运行聚合，您的 count 属性将使用与您的 GroupKey 匹配的文档量填充。整洁！
基于公认的答案，有时您需要指定一个无法使用 Linq IQueryable 接口表示的过滤器，但您也不想诉诸手写 BSON，然后还必须将 BSON 投射回您的对象。您可以将这两个示例结合起来，两全其美。仍然希望你能回来g.First但这也可以。
var statusesCollection = database.GetCollection<Status>("statuses");
var filter = Builders<Status>.Filter.GeoWithinCenterSphere(x => x.LongLatField, longitude, latitude, radians);
var res = await statusesCollection.Aggregate().Match(filter).Group(x => x.PersistantId,
g=>new Status{_id =g.First()._id,
payment = g.Key,
code=g.First().code,
date=g.First().date
}
))
.ToListAsync();
建立在
ocuenca的答案之上：从 Mongo C# 驱动程序版本 2.17 开始，可以直接在 Group() 语句中使用First()：
var statusesCollection = database.GetCollection<Status>("statuses");
var result= statusesCollection.AsQueryable()
.OrderByDescending(e=>e.date)
.Group(e => e.payment, g => g.First())
.ToList();
因此，您不必在投影中创建状态的新实例。
<小时 />在MongoDB C#驱动程序的发行说明中，他们写道：
支持聚合阶段$group$topN和相关累加器
https://mongodb.github.io/mongo-csharp-driver/2.17/what_is_new/
我只是尝试将First()用作累加器，它起作用了。