How to get n records of each group in mongodb using C#, LINQ?

Question

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)

Well, what have you tried so far, what issues have you run into? We're not here to write the code for you. — Vulpex, Mar 03 '21 at 11:35
What's wrong with the query you're using now? Does it not return 10 records from the groups? — haldo, Mar 03 '21 at 11:53
System.NotSupportedException: Specified method is not supported. at MongoDB.Driver.Linq.Processors.AccumulatorBinder.GetAccumulatorArgument(Expression node) — Khairul Alam, Mar 03 '21 at 11:54
@KhairulAlam why are you doing this? `GroupName = g.Key, Items = g.Take(10).ToList()` Can you explain — Vivek Nuna, Mar 03 '21 at 12:27
Does this answer your question? [How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1](https://stackoverflow.com/questions/59456026/how-to-select-top-n-rows-for-each-group-in-a-entity-framework-groupby-with-ef-3) — Drag and Drop, Mar 03 '21 at 13:59

score 3 · Accepted Answer · answered Mar 03 '21 at 16:42

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();

Thanks @ruslan-gilmutdinov, it is working as expected... – Khairul Alam Mar 04 '21 at 08:20 — Khairul Alam, Mar 04 '21 at 08:20

How to get n records of each group in mongodb using C#, LINQ?

1 Answers1

Linked