CODEWITHSUNDEEP
c#mongodbmongodb-.net-driverasqueryable
MagicMax
MagicMax

Reputation: 141

MondoDB+C#+IQueryable - have an array of strings and how to join it to Mongo's collection in the Where clause

Having quite a small experience with Mongo, please help... I have Employee collection (fields are: FirstName, LastName, Title, etc) in the Mondo DB, using C# MongoDB driver and using the collection as IQueryable so I can search for the data by using LINQ.

I need to perform a complex search in Mongo's Employee collection. For example, a user enters SearchText="John Doe CEO", code split this string into an array: ["John", "Doe", "CEO"]. The task is: if any field (FirstName OR LastName OR Title) contains any word from the array, the query should return it as a result.

AFAIR, this statement works fine when I query a simple List:

collection.AsQueryable().Where(e => 
                    allWordsArr.Any(_ => e.FirstName.ToLower().StartsWith(_.ToLower())) ||
                    allWordsArr.Any(_ => e.LastName.ToLower().StartsWith(_.ToLower())) ||
                    allWordsArr.Any(_ => e.Title.ToLower().StartsWith(_.ToLower())))

but when I run the query on the real Mongo DB it fails due to an unsupported operator string[].Any... So I can't pass the array from the C# code into the Mongo DB and then - perform LINQ/search operations, based on the elements of the array provided... It seems like I can't use an array of strings as initial expression (allWordsArr.Any(...))

How can I perform a search by using multiple fields and an array of words? I'd prefer to use collection.AsQueryable(..)+LINQ approach, but if it's not possible - any suggestion is welcome...

Thanks a lot!

Upvotes: 1

Views: 622

Answers (1)

Dĵ ΝιΓΞΗΛψΚ
Dĵ ΝιΓΞΗΛψΚ

Reputation: 5669

don't think it's possible to do with linq/asqueryable. you can do it with find, like so:

var allWords = new[] { "CEO", "Doe", "John" };

var filters = allWords.Select(w =>
        Builders<Employee>.Filter.Regex(e => e.Title, "/^" + w + "/i") |
        Builders<Employee>.Filter.Regex(e => e.FirstName, "/^" + w + "/i") |
        Builders<Employee>.Filter.Regex(e => e.LastName, "/^" + w + "/i"));

var combinedFilter = Builders<Employee>.Filter.Or(filters);

var result = await collection.Find(combinedFilter).ToListAsync();

here's an alternative:

var regex = "/^" + string.Join("|", allWords) + "/i";

var filter = Builders<Employee>.Filter.Regex(e => e.FirstName, regex) |
             Builders<Employee>.Filter.Regex(e => e.LastName, regex) |
             Builders<Employee>.Filter.Regex(e => e.Title, regex);

var result = await collection.Find(filter).ToListAsync();

Upvotes: 1

Related Questions