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

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.