Aggregate Query

How to write aggregate query in C#? Let me know if anything is not clear.

I tried to define filter but it fails:

var filter1 = Builders.Filter.ElemMatch(“Items”, Builders.Filter.And(Builders.Filter.AnyIn(“Items.ItemId”, workItemWithStartToEndRevs.Select(t => t.WorkItemId).ToList()), Builders.Filter.AnyIn(“Items.ItemRevisionNumber”, workItemWithStartToEndRevs.Select(t => t.StartToEndRev).ToList())));

var obj1 = mongoReviewItems.Aggregate().Match(filter1).Project(p => new { ReviewId = p.ReviewId});

Hi @Mursaleen_Fayyaz, and welcome to the forum

The question is not very clear from the post, but I assumed that you’re having some issues to filter based on a document in an array.

For example, if you have the following example document:

{
  "_id": ObjectId("..."),
  "ReviewId": 210,
  "Items": [
    {"ItemId": 100, "Revision": 4 },
    {"ItemId": 101, "Revision": 5 },
    {"ItemId": 101, "Revision": 6}
  ]
}

If you would like to filter documents in the collection where Items.ItemId is 100 and Revision is in range of [3, 4] and only output the ReviewId you could construct the MongoDB query as below:

db.collection.find({"Items":{"$elemMatch":{
                        "ItemId":100, "Revision":{"$in":[3,4]}
                    }}}, {"ReviewId":1});

The query above utilises $elemMatch query operator and will provide output as below:

{"_id": ObjectId(...), "ReviewId": 210}

Using MongoDB .NET/C# driver you could construct this query as follow:

// Class Mappings
class MyDocument
  {
   public ObjectId Id { get; set; }
   public int ReviewId { get; set;}
   public List<Item> Items { get; set; }
  }
class Item
  {
   public int ItemId { get; set; }
   public int Revision { get; set; }
  }

// Query
var revisionIds = new List<int>();
revisionIds.Add(3); 
revisionIds.Add(4); 
            
FilterDefinition<MyDocument> filter = Builders<MyDocument>.Filter.And(
        Builders<MyDocument>.Filter.ElemMatch(x => x.Items, Builders<Item>.Filter
        .And(
           Builders<Item>.Filter.Eq(y => y.ItemId, 100),
           Builders<Item>.Filter.In(y => y.Revision, revisionIds)
        )));
ProjectionDefinition<MyDocument> project = Builders<MyDocument>
        .Projection.Include(x => x.ReviewId);

var results = collection.Find(filter).Project(project).ToList();

If this does not answer your question, please provide:

  • MongoDB server version
  • Example document with relevant fields
  • MongoDB query/aggregation that you have tried (i.e. from mongo shell or Compass)
  • Minimal reproducible code snippet that you have tried (i.e. C# code)
  • Expected output
  • Any error messages, if any

I’d also recommend to enrol in a free online course from MongoDB University M220N: MongoDB for .NET developers to learn more about application development in MongoDB with .NET.

Regards,
Wan.

1 Like