c# - Linq to Entities : using ToLower() on NText fields -

i'm using sql server 2005, case sensitive database..

in search function, need create linq entities (l2e) query "where" clause compare several strings data in database these rules :

1. the comparison "contains" mode, not strict compare : easy string's contains() method allowed in l2e
2. the comparison must case insensitive : use tolower() on both elements perform insensitive comparison.

all of performs ran following exception : "argument data type ntext invalid argument 1 of lower function" on 1 of fields.

it seems field ntext field , can't perform tolower() on that.
able perform case insensitive contains() on ntext field ?

never use .tolower() perform case-insensitive comparison. here's why:

1. it's possibly wrong (your client collation be, say, turkish, , db collation not).
2. it's highly inefficient; sql emitted lower instead of = case-insensitive collation.

instead, use stringcomparison.ordinalignorecase or stringcomparison.currentcultureignorecase:

var q = f in context.foos         f.bar.equals("hi", stringcomparison.ordinalignorecase)         select f; 

but contains() there's problem: unlike equals, startswith, etc., doesn't have overload stringcomparison argument. why? question; ask microsoft.

that, combined sql server's limitation on lower means there's no simple way want.

possible workarounds might include:

• use full text index, , search in procedure.
• use equals or startswith instead, if possible task
• change default collation of column?