MongoDB C# Find documents where List<string> contains value of another List<string>

Question

In the process of creating a filtering feature in my mongoDB i have created the following find query using the mongoDB driver for c#.

return await DefaultCollection
                .Find(c => c.vintageInfo.IsVintage
                           && c.IsPublished
                           && c.AverageRating >= lowerRating
                           && filterArray1.Contains(c.filterSearchParameters.Dosage))       
                .Skip(page * pageSize)
                .Limit(pageSize)
                .ToListAsync();

This is working perfect and it's all great!

Now as we see in the example above i can check if the provided filterArray1 contains the document attribute c.filterSearchParameters.Dosage. Now the problem arises cause, the document further contains a list as follows c.filterSearchParameters.Styles. What i would like to be able to do is check if the list c.filterSearchParameters.Styles contains any value matching filterArray2.

Edit

As per request:

filterSearchParameters looks like:

public class FilterSearchParameters
    {
        public string Dosage { get; set; }
        public List<string> Style { get; set; }
        public List<string> Character { get; set; }
    }

and the filterArray1 and 2 is just an ordinary:

List<string> filterArray1
List<string> filterArray2

To do this i tried:

return await DefaultCollection
                .Find(c => c.vintageInfo.IsVintage
                           && c.IsPublished
                           && c.AverageRating >= lowerRating
                           && filterArray1.Contains(c.filterSearchParameters.Dosage)
                           && filterArray2.Any(x => c.filterSearchParameters.Style.Any(y => y.Equals(x))))      
                .Skip(page * pageSize)
                .Limit(pageSize)
                .ToListAsync();

Adding the line:

filterArray2.Any(x => c.filterSearchParameters.Style.Any(y => y.Equals(x)))

However this throws Unsopported filter exception. So i tried:

return await DefaultCollection
                .Find(c => c.vintageInfo.IsVintage
                           && c.IsPublished
                           && c.AverageRating >= lowerRating
                           && filterArray1.Contains(c.filterSearchParameters.Dosage)
                           && c.filterSearchParameters.Style.Intersect(filterArray2).Any())     
                .Skip(page * pageSize)
                .Limit(pageSize)
                .ToListAsync();

Adding the line :

c.filterSearchParameters.Style.Intersect(filterArray2).Any()

However the error persisted...

Lastly ive found ->

Builders<Entity>.Filter.ElemMatch(
        x => x.filterSearchParameters.Style,
        s => filterArray2.Contains(s))

But havn't been able to incorporate it and try it out with the other filters.

Does anyone know how to solve this problem, or any knowledge that could point me in the direction of some documentation i could read, or say if i'm already looking in the wrong place... Any help is appreaciated, thanks in advance.

Answer 1

In your data model Style property is a regular .NET List which can be serialized and deserialized to MongoDB. The drawback is that even though you can see LINQ Extension methods like Any or Intersect, those method cannot be converted into database query. That's why it fails in the runtime.

To match two arrays in MongoDB you can simply use $in operator

You can try below example in Mongo shell:

db.col.save({ a : [1,3,4]})
db.col.find({ a : { $in: [1,2]})

It works in Mongo query language and you have to do the same thing in C#. Because of that Mongo C# driver introduces AnyIn generic method which expects two generic IEnumerable parameters, first one from your data model and second one as a parameter type.

public FilterDefinition<TDocument> AnyIn<TItem>(Expression<Func<TDocument, IEnumerable<TItem>>> field, IEnumerable<TItem> values);

You can use Builders to create such expression:

Expression<Func<YourTypeClass, bool>> p = c => c.vintageInfo.IsVintage
                  && c.IsPublished
                  && c.AverageRating >= lowerRating
                  && filterArray1.Contains(c.filterSearchParameters.Dosage);

var filterBuilder = Builders<YourTypeClass>.Filter;

var filter = filterBuilder.AnyIn(x => x.filterSearchParameters.Style, filterArray2);

return await DefaultCollection
    .Find(filterBuilder.And(filter, p))
    .Skip(page * pageSize)
    .Limit(pageSize)
    .ToListAsync();