views:

65

answers:

1

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 ?

+1  A: 

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?
Craig Stuntz
Julien N
I'm just trying to tell you what the options are, not asserting that some ideal option for you exists....
Craig Stuntz