How to build dynamic queries for MongoDB in c# using both & and | operators

Question

So i have a list of key value pairs witch the key is a string and the value is an array of strings as follows:

{
    "filters": [
        {"key": "team", "value": ["3","4","7"]},
        {"key": "placement_type", "value": ["facility"]}
        //{"key": "date", "value": ["2021-01-01"]}
        //{"key": "policy", "value": ["something"]} Number of filters may vary
    ]
}

This list of "filters" can have multiple Key Value pairs depending on the number of filters the user has chosen.

So what i need is to dynamically create is the following query:

(builder.Eq("team", "3") | builder.Eq("team", "4") | builder.Eq("team", "7")) & builder.Eq("placement_type", "Facility");

And this is my attempt:

var builder = Builders<ReportDocument>.Filter;

// IF I USE THIS QUERY IT WORKS AS I EXPECT
var hardCodedQuery = (builder.Eq("team", "3") | builder.Eq("team", "4") | builder.Eq("team", "7")) & builder.Eq("placement_type", "Facility");

var dynamicQuery = builder.Empty;
var orFilters = builder.Empty;

foreach (var filter in request.Filters)
{
  if (filter.Value.Length > 1)
  {
     for (int i = 0; i < filter.Value.Length; i++)
     {
        if (i == 0)
        {
          orFilters = builder.Eq(filter.Key, filter.Value[i]); //Had to do this since i have to initiate the variable as builder.Empty and it was adding and EmptyFilterDefinition
        }
        else
        {
          orFilters &= builder.Eq(filter.Key, filter.Value[i]);
        }
      }
      dynamicQuery = builder.Or(orFilters);
   }
   else
   {
      dynamicQuery &= builder.Eq(filter.Key, filter.Value);
   }
}
return await _reportsCollection.Find(dynamicQuery).ToListAsync();

But once i insert the second filter in the variable "orFilters" with the operator "&=" e becomes a MongoDB.Driver.AndFilterDefinition instead of MongoDB.Driver.OrFilterDefinition

So if anyone has a good way to do this please share it guys. I saw a lot of questions similar like mine in stackoverflow but all refer do doing dynamic filters using just the & (And) operator. But since i have a list of strings as value i need to use the | (Or) operator as well.

Thanks

This helped me alot in building a dynamic fuilter for just AND type filters, I haven't tried for OR yet but the dynamic part was a big help — MX313
– MX313, Commented Nov 14, 2022 at 15:24

Munzer · Accepted Answer · 2021-09-10 16:17:50Z

1

Your first compound assignment doesn't match your expected query, in your example you use OR but in your code you are using AND assignment

....
if (filter.Value.Length > 1)
  {
     for (int i = 0; i < filter.Value.Length; i++)
     {
        if (i == 0)
        {
          orFilters = builder.Eq(filter.Key, filter.Value[i]); //Had to do this since i have to initiate the variable as builder.Empty and it was adding and EmptyFilterDefinition
        }
        else
        {
          // this line was changed 
          orFilters |= builder.Eq(filter.Key, filter.Value[i]);
        }
      }
      //also when this line run, you are resetting your filter so pay attention to it, should change
      dynamicQuery = builder.Or(orFilters);
   }
   .....

answered Sep 10, 2021 at 16:17

Munzer

2,3282 gold badges21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to build dynamic queries for MongoDB in c# using both & and | operators

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related