CODEWITHSUNDEEP
c#.netmongodbmongodb-.net-drivercase-insensitive
Andrew Orsich
Andrew Orsich

Reputation: 53695

MongoDB and C#: Case insensitive search

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)));

Upvotes: 54

Views: 57823

Answers (13)

Çağlar Can Sarıkaya
Çağlar Can Sarıkaya

Reputation: 135

My mongodb case insensitive contains error:

First, I used this,

var filter = Builders<Earthquake>.Filter.Where(
    e => e.Location.ToLowerInvariant().Contains(province.ToLowerInvariant())
);

This creates a query like:

MongoQuery -> find - { "find" : "Earthquake", "filter" : { "Location" : /adana/is } }

The issue is that in MongoDB Compass, I can search using { "Location" : /adana/i }, which works, but { "Location" : /adana/is } does not. After investigating, I found that the s flag is added by the library.

When I used .Contains(), the library converted it into a regex matching. The s flag makes the . (dot) in regex match newline characters.

Additionally, my MongoDB setup doesn’t support the s flag, so I switched to this:

var filter = Builders<Earthquake>.Filter.Regex(
    e => e.Location,
    new BsonRegularExpression(province, "i") 
);

This creates a query like:

MongoQuery -> find - { "find" : "Earthquake", "filter" : { "Location" : /adana/i } }

By removing the s flag and using only the i flag for case-insensitivity, the query worked perfectly.

NOTE: mongodb.driver version is 2.13.3 MongoDB version is 3.2.22 :D yes we are in 2024 but we are not able update mongo to the latest version due to issues with the _id field. The _id values we are using are incompatible with newer versions of MongoDB.

Upvotes: 0

SerjG
SerjG

Reputation: 3570

This one works for me (for MongoDB.Driver version 2.22.0+ )

Builders<TEntity>.Filter
.Regex(e => e.FirstName, new BsonRegularExpression($".*{value}.*", "i"))

Also I would recommend to move "i" to constant like BsonRegularExpressionOptions.IgnoreCase

I'm not sure about performance though. Will it cause the full table scan?

Upvotes: 0

dawid debinski
dawid debinski

Reputation: 540

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

Upvotes: 3

Aljohn Yamaro
Aljohn Yamaro

Reputation: 2881

this is exact text search and case insensitive (see this link).

{ “FieldName” : /^keywordHere$/i }

Upvotes: -1

diegosasw
diegosasw

Reputation: 15684

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

  1. 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;
}

Upvotes: 5

Matt Canty
Matt Canty

Reputation: 2465

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.

Upvotes: 47

A_Arnold
A_Arnold

Reputation: 4049

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();

Upvotes: 4

Teilmann
Teilmann

Reputation: 2168

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());

Upvotes: 7

i3arnon
i3arnon

Reputation: 116606

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.

Upvotes: 78

Codewhisperer
Codewhisperer

Reputation: 23

A way to do it is to use the MongoDB.Bson.BsonJavaScript class as shown below

 store.FindAs<Property>(Query.Where(BsonJavaScript.Create(string.Format("this.City.toLowerCase().indexOf('{0}') >= 0", filter.City.ToLower()))));

Upvotes: 0

Kostassoid
Kostassoid

Reputation: 1898

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.

Upvotes: 0

Andrei Andrushkevich
Andrei Andrushkevich

Reputation: 9973

try to use something like this:

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

Upvotes: 17

Thilo
Thilo

Reputation: 262754

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).

Upvotes: 11

Related Questions