how to make a mongo filter search case insensitive in c#

Question

I have this query here:

            var filter =
        builder.Eq("BuyerDetails.Buyer.Email", request.BuyerEmail)
                 | builder.Eq("BuyerDetails.Buyer.FirstName", request.FirstName)
                 & builder.Eq("BuyerDetails.Buyer.Surname", request.LastName);

however the problem is that i am unable anticipate the case of the email, firstname and surname is mongo.

if the request is firstName = "STACK" and LastName = "OVERFLOW"

and the document in mongo is "firstName = "stack" and LastName = "overflow"

it will not match.

how can i filter with and make it case insensitive?

https://www.mongodb.com/developer/products/mongodb/schema-design-anti-pattern-case-insensitive-query-index/ — ProgrammingLlama, Oct 27 '22 at 13:08
thanks but this is for direct querying of a mongodb, i need to do it via an application in c# — dros, Oct 27 '22 at 13:16
Another, probably worse, approach than discussed in the comments and answers is that if the data truly is case insensitive either turn all text to upper or lower case prior to inserting into the DB. Then write all queries in either upper or lower case. When displaying the data then perform formatting as necessary. — KDecker, Oct 27 '22 at 13:27
@dros What exactly do you think the C# Mongo driver does when you use it to make queries? It queries the database, right? Case insensitive indexes and regex are both usable from C# using the Mongo driver. The page I linked to isn't "for direct querying", it just provides language agnostic sample queries that any language's Mongo driver should be able to make. You shouldn't dismiss things so quickly. — ProgrammingLlama, Oct 27 '22 at 13:49

score 0 · Accepted Answer · answered Oct 27 '22 at 13:23

You need to work with the $regex operator. In MongoDB .NET Driver, refer to FilterDefinitionBuilder.Regex Method (FieldDefinition, BsonRegularExpression).

using System.Text.RegularExpressions;

builder.Regex("BuyerDetails.Buyer.FirstName", new Regex(request.FirstName, RegexOptions.IgnoreCase))

builder.Regex("BuyerDetails.Buyer.Surname", new Regex(request.LastName, RegexOptions.IgnoreCase))

Note that the above query will find the document with the field value without guarantee from start to end. Example of illustration:

Search keyword for FirstName: Stack

FirstName	Is Matched?
STACK	Yes
STACKxxx	Yes
xxxSTACK	Yes
xxxSTACKxxx	Yes

If you want to filter the document with the match of the filtered string from start to end (exact match), you need the ^ start and $ end symbols in regex.

var filter =
    builder.Eq("BuyerDetails.Buyer.Email", request.BuyerEmail)
    | builder.Regex("BuyerDetails.Buyer.FirstName", new Regex($"^{request.FirstName}$", RegexOptions.IgnoreCase))
    & builder.Regex("BuyerDetails.Buyer.Surname", new Regex($"^{request.LastName}$", RegexOptions.IgnoreCase));

how to make a mongo filter search case insensitive in c#

1 Answers1