Linq to Entities : using ToLower() on NText fields

Question

I'm using SQL Server 2005, with a case sensitive database..

In a search function, I need to create a Linq To Entities (L2E) query with a "where" clause that compare several strings with the data in the database with these rules :

1. The comparison is a "Contains" mode, not strict compare : easy as the string's Contains() method is allowed in L2E
2. The comparison must be case insensitive : I use ToLower() on both elements to perform an insensitive comparison.

All of this performs really well but I ran into the following Exception : "Argument data type ntext is invalid for argument 1 of lower function" on one of my fields.

It seems that the field is a NText field and I can't perform a ToLower() on that.
What could I do to be able to perform a case insensitive Contains() on that NText field ?

Answer 1

Never use .ToLower() to perform a case-insensitive comparison. Here's why:

1. It's possibly wrong (your client collation could be, say, Turkish, and your DB collation not).
2. It's highly inefficient; the SQL Emitted is LOWER instead of = with a case-insensitive collation.

Instead, use StringComparison.OrdinalIgnoreCase or StringComparison.CurrentCultureIgnoreCase:

var q = from f in Context.Foos
        where f.Bar.Equals("hi", StringComparison.OrdinalIgnoreCase)
        select f;

But for Contains() there's a problem: Unlike Equals, StartsWith, etc., it doesn't have an overload for a StringComparison argument. Why? Good question; ask Microsoft.

That, combined with SQL Server's limitation on LOWER means there's no simple way to do what you want.

Possible workarounds might include:

• Use a full text index, and do the search in a procedure.
• Use Equals or StartsWith instead, if possible for your task
• Change the default collation of the column?