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

Question

see also Differences between LINQ to Objects and LINQ to SQL

We use some queries on our database and ours in memory objects .

What is the best way to make a non-sensitive comparison string 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 is not processed on the SQL server, as far as I can tell, so it can be much slower than

 a == b 

Answer 1

I am not an expert in linq for sql, but you can just use the ToUpperInvariant () method for the string before comparing.

Answer 2

The 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 if you really need to explicitly handle case-sensitivity.

In the collation setting SQL_Latin1_General_CP1_CI_AS - CI stands for case insensitive, and AS stands for accent sensitivity.

Unfortunately, Linq-to-Sql ignores the optional String.Compare() parameters, so you cannot explicitly set case sensitivity for comparison. However, it will work with linq for objects.

If you use case-sensitive sorting, you can use something like SqlMethods.Like(field, "string") to use a LIKE query that is case-insensitive - but this does not translate to objects in linq.

Answer 3

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

I personally did not evaluate each of them for performance, but ToLower and ToUpper are not supported in LINQ to SQL, so the comparison seems to be a good candidate. You can study the translated SQL with a tool like LINQPad, which has a free version and translates the query into the SQL database, and see how it works with the database.

In addition, LINQ to Objects is probably different in that good for one may not be the best for another ...