MongoDB and C#: Case insensitive search

Question

I am using MongoDB and the C# driver for MongoDB.

I recently discovered that all queries in MongoDB are case-sensitive. How can I make a case-insensitive search?

I found one way to do this:

Query.Matches(
    "FirstName", 
    BsonRegularExpression.Create(new Regex(searchKey,RegexOptions.IgnoreCase)));

Answer 1

The simplest and safest way to do that is using Linq:

var names = namesCollection.AsQueryable().Where(name =>
    name.FirstName.ToLower().Contains("hamster"));

As explained in the tutorial ToLower, ToLowerInvariant, ToUpper and ToUpperInvariant all perform matches in a case insensitive way. After that you can use all the supported string methods like Contains or StartsWith.

This example will generate:

{
    "FirstName" : /hamster/is
}

The i option makes it case insensitive.

Answer 2

I've just implemented this much simpler than any of the other suggestions. However I realise due to the age of this question, this functionality may not have been available at the time.

Use the options of the Bson Regular Expression constructor to pass in case insensitivity. I just had a look at the source code and found that 'i' is all you need. For example.

var regexFilter = Regex.Escape(filter);
var bsonRegex = new BsonRegularExpression(regexFilter, "i");

Query.Matches("MyField", bsonRegex);

You shouldn't have to keep records twice for searching.

Answer 3

try to use something like this:

Query.Matches("FieldName", BsonRegularExpression.Create(new Regex(searchKey, RegexOptions.IgnoreCase)))

Answer 4

You will probably have to store the field twice, once with its real value, and again in all lowercase. You can then query the lowercased version for case-insensitive search (don't forget to also lowercase the query string).

This approach works (or is necessary) for many database systems, and it should perform better than regular expression based techniques (at least for prefix or exact matching).

Answer 5

As i3arnon answered, you can use Queryable to do a case insensitive comparison/search. What i found out was, that i could not use string.Equals() method, because is it not supported. If you need to do a comparison, Contains() will unfortunately not be suitable which kept me struggling for a solution, for quite some time.

For anyone wanting to do a string comparison, simply use == instead of .Equals().

Code:

var names = namesCollection.AsQueryable().Where(name =>
    name.FirstName.ToLower() == name.ToLower());

Answer 6

You can also use MongoDB's built in filters. It may make it easier for using some of mongo's methods.

var filter = Builders<Model>.Filter.Where(p => p.PropertyName.ToLower().Contains(s.ToLower()));
var list = collection.Find(filter).Sort(mySort).ToList();

Answer 7

For MongoDB 3.4+ the recommended way is to use indexes. See https://jira.mongodb.org/browse/DOCS-11105?focusedCommentId=1859745&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-1859745

I am successfully searching with case insensitive by: 1. Creating an index with Collation for a locale (e.g: "en") and with a strength of 1 or 2. See https://docs.mongodb.com/manual/core/index-case-insensitive/ for further details

2. Using the same Collation when performing searches on the MongoDb collection.

As an example:

Create a collation with strength 1 or 2 for case insensitive

private readonly Collation _caseInsensitiveCollation = new Collation("en", strength: CollationStrength.Primary);

Create an index. In my case I index several fields:

private void CreateIndex()
{
    var indexOptions = new CreateIndexOptions {Collation = _caseInsensitiveCollation};
    var indexDefinition
        = Builders<MyDto>.IndexKeys.Combine(
            Builders<MyDto>.IndexKeys.Ascending(x => x.Foo),
            Builders<MyDto>.IndexKeys.Ascending(x => x.Bar));
    _myCollection.Indexes.CreateOne(indexDefinition, indexOptions);
}

When querying make sure you use the same Collation:

public IEnumerable<MyDto> GetItems()
{
    var anyFilter = GetQueryFilter();
    var anySort = sortBuilder.Descending(x => x.StartsOn);  
    var findOptions = new FindOptions {Collation = _caseInsensitiveCollation};

    var result = _salesFeeRules
        .Find(anyFilter, findOptions)
        .Sort(anySort)
        .ToList();

    return result;
}

Answer 8

The easiest way for MongoDB 3.4+ is to use one of ICU Comparison Levels

return await Collection()
.Find(filter, new FindOptions { Collation = new Collation("en", strength: CollationStrength.Primary) })
.ToListAsync();

More info https://docs.mongodb.com/manual/reference/method/cursor.collation/index.html

Answer 9

In case anyone else wondering, using fluent-mongo add-on, you can use Linq to query like that:

public User FindByEmail(Email email)
{
    return session.GetCollection<User>().AsQueryable()
           .Where(u => u.EmailAddress.ToLower() == email.Address.ToLower()).FirstOrDefault();
}

Which results in correct JS-query. Unfortunately, String.Equals() isn't supported yet.