15

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 ?

Julien N
  • 3,880
  • 4
  • 28
  • 46

3 Answers3

31

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
  • 125,891
  • 12
  • 252
  • 273
  • hum... I guess you tell me that using ToLower() is wrong but I have no other solution as : - Changing the collation is too much work & risk just for some search criteria - Equals & StartsWith won't suit the need here. I'm thinking about converting the NText field to NVarchar and limit user input... Performance isn't really important in my case. – Julien N Mar 12 '10 at 17:22
  • I'm just trying to tell you what the options are, not asserting that some ideal option for you exists.... – Craig Stuntz Mar 12 '10 at 18:11
6

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"));
Ira Klotzko
  • 69
  • 1
  • 2
  • 1
    This is Linq to Entities : the goal is to generate an appropriate SQL request. Your solution will grab all the rows of the table (or the rows filtered with the other criteria) and then apply the filter. Indeed this is absolutely not efficient :). But I admit that it will work ! – Julien N Oct 10 '12 at 14:46
  • 1
    I thought the goal was an appropriate linq to entities? In terms of efficiency... Sometimes when your client is screaming to fix the damn bug and your boss is tapping their fingers giving you the stink eye... Efficiency and performance are highly overrated except in cases where they aren't. Just because something can be optimized doesn't mean it has to be. – Ira Klotzko Oct 10 '12 at 14:57
  • 2
    Agreed, I just mentioned it so that new people using this technique knows what it really does (I'm always surprised to see how many people use L2E without knowing what happens underneath). So with big tables, it might have a significant performance / memory impact. In my case, the performance was not important. – Julien N Oct 10 '12 at 16:03
  • 1
    Absolutely, people have to know, good point. This wouldn't be appropriate for a large amount of data. – Ira Klotzko Oct 15 '12 at 13:34
0

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!

Benny Ae
  • 1,897
  • 7
  • 25
  • 37