Linq to Entities : using ToLower() on NText fields

问题:

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:

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?

回答2:

Use a lambda expression here and create an intermediary list that can handle the lower clause.

var q = Context.Foos.ToList().Where(s => s.Bar.ToLower().Contains("hi"));

Not terribly efficient, but it does work. If you have additional predicates in your where clause then it works to your advantage:

var q = Context.Foos.Where(p => p.f1 == "foo" && p.f2 == "bar").
            ToList().Where(s => s.Bar.ToLower().Contains("hi"));

回答3:

as we known , this is a very "bugged" situation. and it bugs me a lot.

Today, i decide to create a view as:

select * from tableName where theColumn like '%key%'

then load this view into EF.

life is getting easy!