Query data with case-insensitivity string comparison in Linq

When building a custom email duplication detection, I need to query all records that match an input email ignoring case. I tried following approaches:

  • String.Equals with the second parameter StringComparison.OrdinalIgnoreCase:

Where(p => p.new_Email.Equals(txtEmail, StringComparison.OrdinalIgnoreCase))

  • Transform values to lower case before comparison:

Where(p => p.new_Email.ToLower().Equals(txtEmail.ToLower()))

Both attempts threw an exception

“Invalid ‘where’ condition. An entity member is invoking an invalid property or method.”

That’s because the Linq provider can’t translate the query to a valid QueryExpression which should match FetchXML conditional operators.

Interestingly, string comparison in QueryExpression ignores case by default, so Where(p => p.new_Email.Equals(txtEmail)) will work as we want.

For the other way around, if you want case sensitivity, you should add your logic to compare the result(s) of the query with your string.

Let’s gain more CRM EXP!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s