Use LINQ in C# to find MondoDB records when values in a list field match a criteria value from a list

Question

I want to use LINQ to return all records in a MongoDB collection where the field in the record is a list of strings and any string in the list matches any string value in a list of strings used as the search criteria:

Mongo Record in Collection ("Item"):

{
    "_id": ...,
    "StringList": [
        "string1",
        "string2",
        "string3"
    ],
    ...
}

Search Criteria:

var criteria = new List<string> { "string2", "string4" };

My Code:

var foundItems = iMongoDataProvider.FindAll<Item>()
                           .Where(x =>x.StringList.ContainsAny(criteria)).ToList();

Based on the above, the Mongo record should be returned since one of the StringList values matches one of the values in the search criteria. Nothing is returned even though I can manually peruse the collection and find the matching record. What am I doing wrong? Can someone provide an example that will do what I need? Thanks!

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

What you are looking for is ElemMatch Filter (https://docs.mongodb.com/v3.2/reference/operator/query/elemMatch/) :

var foundItems = collection.Find(Builders<Item>.Filter.ElemMatch(
                     x => x.StringList,
                     s=>criteria.Contains(s)));

where collection is your IMongoCollection<Item> I see, that you are using FindAll, that means, that your MongoDb driver is of the version 1.x (see here more about it: FindAll in MongoDB .NET Driver 2.0) I would suggest to update your driver, because this version isnot uptodate. Or are the any important reason don't to do it?

This filter query on server. Sure you could get your data as IEnumerable and filter it locally:

var foundItems = collection.Find(x=>true)
                   .ToEnumerable()
                   .Where(x => x.StringList.Intersect(criteria).Any());

If your data is not so huge and you are fine with filtering on client it's a good way too.

And if you are doing already FindAll, that means you get get all the data, you could query it with intersect :

var foundItems = iMongoDataProvider.FindAll<Item>()
                           .Where(x => x.StringList.Intersect(criteria).Any());

edited May 23 '17 at 11:53

Community

1
1

answered Jan 05 '17 at 18:03

Maksim Simkin

9,561
4
36
49

Tried to use your first suggestion, but got a `Cannot resolve symbol 'Builders'`; got the same message for `ToEnumerable` for your second suggestion. Not sure what I am missing. – linebam59 Jan 07 '17 at 18:38
@MarkLinebarger You are missing new version of the driver, i have updated my answer. Sorry, all what i wrote was for the 2.x MongoDb c# drvier – Maksim Simkin Jan 07 '17 at 18:45
We are on version 1.11.0.92. We have not done any research on the differences when upgrading to 2.x (and any issues it may cause), so I cannot upgrade without consulting the team. I do know that `FindAll` is used in many places throughout our solution; if that changed dramatically, it's not worth changing to resolve this one issue. I do know that my code works if I find all based on **one** of the _criteria_ entries; i.e.: `var foundItems = iMongoDataProvider.FindAll().Where(x => x.StringList.Contains(criteria[0])).ToList();` – linebam59 Jan 07 '17 at 19:22
@MarkLinebarger I have updated my answer, as i understood FindAlljust gives you all Items, so it's not mongodb, but Linq question and intersect query should give valid results. – Maksim Simkin Jan 07 '17 at 19:37

score 0 · Answer 2 · answered Jan 05 '17 at 17:54

0

have you tried somethinglike:

using System;
using System.Collections.Generic;
using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
using System.Linq;
using System.Linq.Expressions;

var foundItems = _collection.FindAll(x=> criteria.Any(cc=> xx.StringList.Contains(cc))).ToList();

Where _collection is IMongoCollection<TEntity> _collection

answered Jan 05 '17 at 17:54

federico scamuzzi

3,708
1
17
24

is the variable `xx` declared? – Hogan Jan 05 '17 at 18:07
I don't understand this response. As @Hogan asked yesterday, I see no declaration for `xx`... should that be `x`? If so, then that didn't work. Please explain. – linebam59 Jan 07 '17 at 18:26

Hogan · Answer 3 · 2017-01-07T18:35:05.833

-1

What you want is to know if the intersection of the two lists has any values:

 .Where(x =>x.StringList.Intersect(criteria).Any())

I'm not sure what the problem is with your code but here is working code

void Main()
{
    List<string> []StringList = new List<string>[] {
             new List<string> {    "string1", "string2", "string3" },
             new List<string> {    "string11", "string12", "string13" },
             new List<string> {    "string21", "string22", "string4" }
    };

    var criteria = new List<string> { "string2", "string4" };

    var foundItems = StringList
                      .Where(x => x.Intersect(criteria).Any()).ToList();

    foundItems.Dump();
}

I tested this using LinqPad (which I recommend to anyone working in Linq and it is free).

edited Jan 07 '17 at 18:35

answered Jan 05 '17 at 18:03

Hogan

69,564
10
76
117

This, from the looks of it, seemed like my best option, but I get this error when running: "Unable to determine the serialization information for the expression: Enumerable.Intersect(x.StringList, System.Collections.Generic.List`1[System.String])." I am missing something. – linebam59 Jan 07 '17 at 02:30
@MarkLinebarger not sure, I'd have to see all the source to be sure but it should be the same as `.Where(x => criteria.Intersect(x.StringList).Any())` does that give you the same error? – Hogan Jan 07 '17 at 05:09
I get the same general error... here is the full error for the second try: 'System.NotSupportedException was unhandled by user code HResult=-2146233067 Message=Unable to determine the serialization information for the expression: Enumerable.Intersect(System.Collections.Generic.List`1[System.String], x.StringList). Source=MongoDB.Driver' – linebam59 Jan 07 '17 at 18:18
@MarkLinebarger -- see my edit -- how is your code different from this working example? – Hogan Jan 07 '17 at 18:35
Perhaps my issue is trying to do this with MondoDB. As noted in my original question, I am perusing a collection in Mongo that includes a field (_StringList_) that is an array of strings. In the code (above), I'm trying to `FindAll` items where **any value** found in the string array matches **any value** that can be found in the List (_criteria_). I don't know if MongoDB.Drivers allow for this type of Linq expression. Any ideas on that? – linebam59 Jan 07 '17 at 19:03
@MarkLinebarger -- oh yeah Maksim Simkin explained it well in his answer, use that. – Hogan Jan 07 '17 at 19:04
His suggestion requires updating our MongoDB drivers; I will be checking into that. Thanks for your help. – linebam59 Jan 07 '17 at 19:28

Use LINQ in C# to find MondoDB records when values in a list field match a criteria value from a list

3 Answers3

Linked