Case insensitive string compare in LINQ-to-SQL

Question

I've read that it's unwise to use ToUpper and ToLower to perform case-insensitive string comparisons, but I see no alternative when it comes to LINQ-to-SQL. The ignoreCase and CompareOptions arguments of String.Compare are ignored by LINQ-to-SQL (if you're using a case-sensitive database, you get a case-sensitive comparison even if you ask for a case-insensitive comparison). Is ToLower or ToUpper the best option here? Is one better than the other? I thought I read somewhere that ToUpper was better, but I don't know if that applies here. (I'm doing a lot of code reviews and everyone is using ToLower.)

Dim s = From row In context.Table Where String.Compare(row.Name, "test", StringComparison.InvariantCultureIgnoreCase) = 0

This translates to an SQL query that simply compares row.Name with "test" and will not return "Test" and "TEST" on a case-sensitive database.

Answer 1

If you pass a string that is case-insensitive into LINQ-to-SQL it will get passed into the SQL unchanged and the comparison will happen in the database. If you want to do case-insensitive string comparisons in the database all you need to to do is create a lambda expression that does the comparison and the LINQ-to-SQL provider will translate that expression into a SQL query with your string intact.

For example this LINQ query:

from user in Users
where user.Email == "[e-mail]"
select user

gets translated to the following SQL by the LINQ-to-SQL provider:

SELECT [t0].[Email]
FROM [User] AS [t0]
WHERE [t0].[Email] = @p0
-- note that "@p0" is defined as nvarchar(11)
-- and is passed my value of "[e-mail]"

As you can see, the string parameter will be compared in SQL which means things ought to work just the way you would expect them to.

Answer 2

where row.name.StartsWith(q, true, System.Globalization.CultureInfo.CurrentCulture)

Answer 3

To perform case sensitive Linq to Sql queries declare ‘string’ fields to be case sensitive by specifying the server data type by using one of the following;

varchar(4000) COLLATE SQL_Latin1_General_CP1_CS_AS 

or

nvarchar(Max) COLLATE SQL_Latin1_General_CP1_CS_AS

Note: The ‘CS’ in the above collation types means ‘Case Sensitive’.

This can be entered in the “Server Data Type” field when viewing a property using Visual Studio DBML Designer.

For more details see http://yourdotnetdesignteam.blogspot.com/2010/06/case-sensitive-linq-to-sql-queries.html