Need to get 10 items of each group. Order By Id decending and Group By Category
public class Item
{
public string Id { get; set ; }
public string Name { get ; set ; }
public string Category { get ; set ; }
}
I tryied something like this.
var data = Collection.AsQueryable()
.OrderByDescending(o=> o.Id)
.GroupBy(x => x.Category)
.Select(g => new { GroupName = g.Key, Items =
g.Take(10).ToList() });
But got exception like this
System.NotSupportedException: Specified method is not supported. at MongoDB.Driver.Linq.Processors.AccumulatorBinder.GetAccumulatorArgument(Expression node)
Use Aggregation Framework instead. MongoDB LINQ provider is built on top of it.
You can get 10 items of each group ordered by Id descending with the following query:
db.items.aggregate([
{ "$sort": { "Id": -1 } },
{ "$group": { "_id": "$Category", "Items": { "$push": "$$ROOT" } } },
{ "$project": { "_id": 0, "GroupName": "$_id", "Items": { "$slice": ["$Items", 10] } } }
])
C# code will be as follows:
// models
public class Item
{
public string Id { get; set; }
public string Name { get; set; }
public string Category { get; set; }
}
public class ItemsGroup
{
public string GroupName { get; set; }
public Item[] Items { get; set; }
}
// query
var collection = db.GetCollection<Item>("Items");
IAggregateFluent<ItemsGroup> result = collection.Aggregate()
.SortByDescending(o => o.Id)
.Group(BsonDocument.Parse("{ _id: '$Category', Items: { '$push': '$$ROOT'}}"))
.Project<ItemsGroup>(BsonDocument.Parse("{ _id: 0, GroupName: '$_id', Items: { $slice: ['$Items', 10]}}"));
List<ItemsGroup> groups = result.ToList();
This query, however, may have a problem. If there are thousands of items for each group, I guess the group stage will keep them all in the Items temporary array, while we just want 10.
If the number of keys being grouped is not large then it would be better to do $lookup for each group instead of pushing everything into one array and then $slicing it. This can be achieved by the following query:
aggregate([
{ "$group": { "_id": "$Category" } },
{ "$lookup": {
"from": "Items",
"let": { "c": "$_id" },
"as": "Items"
"pipeline": [
{ "$match": { "$expr": { "$eq": ["$Category", "$$c"] } } },
{ "$sort": { "Id": -1 } },
{ "$limit": 10 }
],
}
},
{ "$project": { "_id": 0, "GroupName": "$_id", "Items": 1 } }
])
In C# code, it would be like:
BsonArray pipeline = new BsonArray
{
BsonDocument.Parse("{ $match: { $expr: { $eq: ['$Category', '$$c']} } }"),
BsonDocument.Parse("{ $sort: { Id: -1 } }"),
BsonDocument.Parse("{ $limit: 10 }")
};
BsonDocument lookup = new BsonDocument("$lookup",
new BsonDocument("from", "Items")
.Add("let", new BsonDocument("c", "$_id"))
.Add("pipeline", pipeline)
.Add("as", "Items")
);
IAggregateFluent<ItemsGroup> result = collection.Aggregate()
.Group(BsonDocument.Parse("{ _id: '$Category' }"))
.AppendStage<object>(lookup)
.Project<ItemsGroup>("{ _id: 0, GroupName: '$_id', Items: 1 }");
List<ItemsGroup> groups = result.ToList();