How to optimise this mongodb query in C#?

Question

SELECT 
Member_strMobileNo,
strMembername
FROM tblMembers WITH (NOLOCK)
WHERE CHARINDEX (LOWER(@strMembername), LOWER(strMembername)) > 0
OR CHARINDEX (LOWER(@strMobileNo), LOWER(Member_strMobileNo)) > 0

This is my SQL query I want to fetch data from MongoDB using C# driver .. My Json Structure is :

{
 "memberName" : "seema",
 "Email" : seema@gmail.com
  "Mob"  : 9876543
}

Actually this query is used fro searching details.

In mongo i tried this ,

 var query = Query.Or(Query.In("memberName",  BsonRegularExpression.Create(String.Format("/{0}/i", strMemberName)) ),
                                     new QueryDocument("Mob", BsonRegularExpression.Create(String.Format("/{0}/i", strMobileNo))).

But i guess this query will not be fine since because of resource expensive BsonRegularExpression.Check this this answer in that.

I am using c#, mongoDb, mongodb C# native driver.

How to optimise this.. I am new to mongoDb.. Thanks for all replies..

Don't use regexes, simply normalize the string to a consistent casing in the same way you're doing it in SQL. — mnemosyn, Nov 12 '13 at 11:09
This seems like a better question for http://codereview.stackexchange.com/ — Liam, Nov 12 '13 at 11:10

score 1 · Answer 1 · answered Nov 20 '13 at 17:57

1

You can do this with an aggregate, project $toLower See: http://docs.mongodb.org/manual/reference/operator/aggregation/toLower/

Something like: db.posts.aggregate([{$project:{date2:"$date",title:{$toLower:"$title"}}}

answered Nov 20 '13 at 17:57

Alan Spencer

566
3
12

score 0 · Answer 2 · answered Nov 14 '13 at 12:00

0

There is no easy way to do it efficiently with MongoDB.

I suggest you maintain a lower case version of your fields (eg. normalizedMemberName), have them indexed and use them for your queries.

answered Nov 14 '13 at 12:00

Reda

2,289
17
19

How to optimise this mongodb query in C#?

2 Answers2