Case insensitive string compare with linq-to-sql and linq-to-objects

Question

see also Differences between LINQ to Objects and LINQ to SQL queries

We are using the some queries over our database and our in memory objects.

What is the best way of doing an insensitive string compare with linq-to-sql so that?

• It runs fast on SQL Server
• The same query expression can be used with linq-to-objects to get the same result

Using

a.ToLowerInvariant() == b.ToLowerInvariant()

at least gets the same results, but it does not get processed on SQL server as far as I can tell, so can be a lot slower then

a == b

Answer 1

I'm not an expert in linq to sql, but you could just use the ToUpperInvariant() method of string before compareing.

Answer 2

Hey,

Here are the list of supported string operations in LINQ to SQL, which would obviously work in LINQ to objects: http://msdn.microsoft.com/en-us/library/bb882672.aspx

I personally haven't evaluated each for performance, but ToLower and ToUpper aren't supported in LINQ to SQL, so it seems like Compare is a good candidate. You can explore the translated SQL by using a tool like LINQPad, which has a free version and translates a query into database SQL, and see how it runs against the DB.

Also, LINQ to Objects probably differs so what's good for one may not be good for the other...

Answer 3

Case sensitivity in your SQL database is determined by the collation setting. By default, I think most databases are case insensitive, so you should check whether you actually need to handle case sensitivity explicitly.

In a collation setting of SQL_Latin1_General_CP1_CI_AS - CI stands for case insensitive and AS stands for accent sensitive.

Unfortunately, Linq-to-Sql ignores the extra parameters of String.Compare() so you won't be able to explicitly set the case sensitivity to compare with. It will work with linq to objects however.

If you use a case sensitive collation, you could use something like SqlMethods.Like(field, "string") to use a LIKE query - which is case insensitive -, but that doesn't translate into linq to objects.