How to write this aggregate query in C#?

Mursaleen_Fayyaz · July 21, 2020, 10:23am

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

wan · July 24, 2020, 7:24am

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.

Mursaleen_Fayyaz · August 12, 2020, 9:34am

Thanks wan. you understand correctly. But my query input is List of object
and object is

[Serializable]
    [DataContract(Name = "WorkItemWithStartToEndRev", Namespace = "")]
    public class WorkItemWithStartToEndRev
    {
        [DataMember(Name = "workItemId")]
        public int WorkItemId { get; set; }


        [DataMember(Name = "startToEndRev")]
        public List<int> StartToEndRev { get; set; }
    }

I want to find each object of List in each document .
your provided solution find single object in all document in collection.

Mursaleen_Fayyaz · August 12, 2020, 10:14am

I also tried with mongodb mapreduce for solving this:
Following is the code:
Please review my following code and tell me what's wrong which causing exception. Thanks

 [Serializable]
    [DataContract(Name = "WorkItemWithStartToEndRev", Namespace = "")]
    public class WorkItemWithStartToEndRev
    {
        [DataMember(Name = "workItemId")]
        public int WorkItemId { get; set; }


        [DataMember(Name = "startToEndRev")]
        public List<int> StartToEndRev { get; set; }
    }

    [Serializable]
    [DataContract(Name = "ReviewMetaInfo ", Namespace = "")]
    public class ReviewMetaInfo
    {

        public static string ReviewCollectionName
        {
            get { return "ReviewMetaInfos"; }
        }


        [DataMember(Name = "reviewId")]
        public int ReviewId { get; set; }


        [DataMember(Name = "items")]
        public List<ReviewItemInfo> Items { get; set; }
    }

   
    [Serializable]
    [DataContract(Name = "ReviewItemInfo ", Namespace = "")]
    public class ReviewItemInfo
    {
       
        [DataMember(Name = "itemId")]
        public int ItemId { get; set; }


        [DataMember(Name = "itemRevisionNumber")]
        public int ItemRevisionNumber { get; set; }
   }

public OperationResult GetReviewIdsFromMongo(List<WorkItemWithStartToEndRev> workItemWithStartToEndRevs, string projectId)
        {
            OperationResult result = new OperationResult(OperationStatusTypes.Failed);


            DebugLogger.LogStart("MongoReviewController", "GetReviewIdsFromMongo");
           
            IMongoDatabase database = this.mMongoClient.GetDatabase(this.mDbName);


            IMongoCollection<ReviewMetaInfo> mongoReviewItems = database.GetCollection<ReviewMetaInfo>(ReviewMetaInfo.ReviewCollectionName);
           
            var jsonObject = CommonUtility.Serialize(workItemWithStartToEndRevs);




            var scope = new BsonDocument("workItemWithStartToEndRevs", jsonObject);


            var map = new BsonJavaScriptWithScope(@"
                                function() {
                                               
                                               
                                                for (var i = 0; i < this.Items.length; i++) {


                                                    for(var item in workItemWithStartToEndRevs)
                                                    {
                                                        if(workItemWithStartToEndRevs[item].workItemId == this.Items[i].ItemId && workItemWithStartToEndRevs[item].startToEndRev.includes(this.Items[i].ItemRevisionNumber))
                                                        {
                                                            emit(this.Items[i].ItemId, { reviewId: this.ReviewId, workItemRevId: this.Items[i].ItemRevisionNumber });
                                                            break;
                                                        }
   
                                                    }
                                                   


                                                }




                                               
                                            }", scope);


            var reduce = new BsonJavaScriptWithScope(@"        
                                function(key, values) {
                                       
                                                        var result = []
       
                                                        for(var i in values) {    


                                                            var item = values[i];  


                                                            result.push({
                                                                            'reviewId' : item.reviewId,
                                                                            'workItemId'  : key,
                                                                            'revId'       : item.workItemRevId
                                                                        });
                                                        }


                                                        return result;
       
                                                        }", scope);


         


            try
            {
                var results = mongoReviewItems.MapReduce<BsonDocument>(map, reduce);
            }
            catch (Exception ex)
            {


               
            }
}

Mursaleen_Fayyaz · August 18, 2020, 2:51pm

Please suggestion solution for the give query.

wan · August 21, 2020, 1:16am

Hi @Mursaleen_Fayyaz,

Again, it’s not very clear what you’re asking here. Are you wanting to query with a list as below example:

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

If so, you could utilise the Or builder, for example:

var revisionIds1 = new List<int>();
revisionIds1.Add(3); 
revisionIds1.Add(4); 
            
var revisionIds2 = new List<int>();
revisionIds2.Add(1); 
revisionIds2.Add(2); 

FilterDefinition<MyDocument> filter = Builders<MyDocument>.Filter.And(
      Builders<MyDocument>.Filter.ElemMatch(x => x.Items, Builders<Item>.Filter.Or(
              Builders<Item>.Filter.And(
                      Builders<Item>.Filter.Eq(y => y.ItemId, 100),
                      Builders<Item>.Filter.In(y => y.Revision, revisionIds1)
              ), 
              Builders<Item>.Filter.And(
                      Builders<Item>.Filter.Eq(y => y.ItemId, 200),
                      Builders<Item>.Filter.In(y => y.Revision, revisionIds2)
               )
      ) )
 );
ProjectionDefinition<MyDocument> project = Builders<MyDocument>
         .Projection.Include(x => x.Reviewid);
var results = collection.Find(filter).Project(project).ToList();

If this doesn’t answer your question could you elaborate the question with the following:

Example document with relevant fields
MongoDB query/aggregation that you have tried (i.e. from mongo shell or Compass)
The output that you’re getting
The expected output

Providing good information to clarify your question helps others to answer your question better.

Regards,
Wan

Mursaleen_Fayyaz · August 21, 2020, 6:51am

Hi wan,

This is my object

> [Serializable]

[DataContract(Name = "WorkItemWithStartToEndRev", Namespace = "")]
public class WorkItemWithStartToEndRev
{
    [DataMember(Name = "workItemId")]
    public int WorkItemId { get; set; }


    [DataMember(Name = "startToEndRev")]
    public List<int> StartToEndRev { get; set; }
}

and i have List of above object

List of WorkItemWithStartToEndRev

and the length of list is dynamic.

The length of the array is dynamic. How many “and” filters we apply in “or” filter.

[
{“ItemId”:100, “Revision”:{“$in”:[3,4]}},
{“ItemId”:200, “Revision”:{“$in”:[1,2]}},
]

wan · August 25, 2020, 12:10am

Hi @Mursaleen_Fayyaz,

I don’t quite understand what you’re trying to ask here.

If you have a list of WorkItemWithStartToEndRev then you just have to convert them into FilterDefinition object so that you could pass that on to Find(). Alternatively, you could serialise the object into JSON and parse using BsonDocument.Parse() to convert into BsonDocument.

Regards,
Wan.

Mursaleen_Fayyaz · September 17, 2020, 10:04am

Hello,

I don’t know why you are not understanding the actual problem.

Let me explain you the problem again. For making it simple

Following is my C# models for querying mongo collection:

and we have list of WorkItemWithStartToEndRev object with n number of items.

now my mongo collection C# object is ReviewMetaInfo and it contains Items property of List type of object ReviewItemInfo

Now, what query and filter(s) will check all items of List of “WorkItemWithStartToEndRev” in each document of mongo collection object “ReviewMetaInfo” and projection ReviewId list.

what we will use in mongo for the above type of query models

Simple mongo query with filter definition
Aggregation Pipeline
Mongo map reduce with C#

I would really appreciate your reply on this long running problem.

Thanks,
Mursaleen