How to fetch filtered document based on List of objetcs in Mongodb using C# mongodb driver

Question

Consider this scenario: I wanted to fetch the filtered document from the documents collection of Student.

[  
   {  
      name:'arun',
      gender:'Male',
      physics:88,
      maths:87,
      english:78
   },
   {  
      name:'rajesh',
      gender:'Male',
      physics:96,
      maths:100,
      english:95
   },
   {  
      name:'moorthy',
      gender:'Male',
      physics:89,
      maths:90,
      english:70
   }]

I wanted to filter based on array having value as

[  
       {  
          name:'arun',
          gender:'Male',
          physics:88,
          maths:87,
          english:78
       },
       {  
          name:'rajesh',
          gender:'Male',
          physics:96,
          maths:100,
          english:95
       }]

And I wanted to match name, gender and physics to get the filtered documents. I don't want to use foreach. How can I get a filtered list of documents?

Answer 1

This query will be slightly tricky as it requires matching any item and at the same time, all the properties in the item must be matched.

db.collection.find({
  $expr: {
    $or: [
      {
        $and: [
          {
            $eq: [
              "$name",
              "arun"
            ]
          },
          {
            $eq: [
              "$gender",
              "Male"
            ]
          },
          {
            $eq: [
              "$physics",
              88
            ]
          }
        ]
      },
      {
        $and: [
          {
            $eq: [
              "$name",
              "rajesh"
            ]
          },
          {
            $eq: [
              "$gender",
              "Male"
            ]
          },
          {
            $eq: [
              "$physics",
              96
            ]
          }
        ]
      }
    ]
  }
})

Demo @ Mongo Playground

In the MongoDB .NET way, you need to prepare the filter definition matching all the properties in the student first and add it into filterDefinitions. Next, with Or build the query that matches any item in filterDefinitions.

List<FilterDefinition<Student>> filterDefinitions = new List<FilterDefinition<Student>>();
foreach (Student student in searchStudents)
{
    FilterDefinition<Student> studentFilter = FilterDefinition<Student>.Empty;
    studentFilter &= Builders<Student>.Filter.Eq(x => x.Name, student.Name)
        & Builders<Student>.Filter.Eq(x => x.Gender, student.Gender)
        & Builders<Student>.Filter.Eq(x => x.Physics, student.Physics);

    filterDefinitions.Add(studentFilter);
}

List<Student> result = (await _collection.FindAsync(Builders<Student>.Filter.Or(filterDefinitions)))
    .ToList();