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" : [email protected]
  "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
– mnemosyn, Commented Nov 12, 2013 at 11:09
This seems like a better question for codereview.stackexchange.com — Liam
– Liam, Commented Nov 12, 2013 at 11:10

Alan Spencer · Accepted Answer · 2013-11-20 17:57:23Z

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, 2013 at 17:57

Alan Spencer

5663 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Reda · Accepted Answer · 2013-11-14 12:00:33Z

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, 2013 at 12:00

Reda

2,28918 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to optimise this mongodb query in C#?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related