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 == "[email protected]"
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 "[email protected]"

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

As you say, there are some important differences between ToUpper and ToLower, and only one is dependably accurate when you're trying to do case insensitive equality checks.

Ideally, the best way to do a case-insensitive equality check is:

String.Equals(row.Name, "test", StringComparison.OrdinalIgnoreCase)

Note the OrdinalIgnoreCase to make it security-safe. But exactly the type of case (in)sensitive check you use depends on what your purposes is. But in general use Equals for equality checks and Compare when you're sorting, and then pick the right StringComparison for the job.

Michael Kaplan (a recognized authority on culture and character handling such as this) has relevant posts on ToUpper vs. ToLower:

• http://blogs.msdn.com/michkap/archive/2007/10/01/5218976.aspx
• http://blogs.msdn.com/michkap/archive/2005/03/10/391564.aspx

He says "String.ToUpper – Use ToUpper rather than ToLower, and specify InvariantCulture in order to pick up OS casing rules"

Answer 3

LINQ to SQL will translate a Contains() to a SQL LIKE statement which is not case sensitive (at least by default, I'm not sure if there is a way to make it case sensitive). This doesn't quite do the job, but if you do Contains() and match the length on the 2 strings you are comparing then they would be the same. I'm not sure how efficient matching the lengths is though.

Answer 4

I used System.Data.Linq.SqlClient.SqlMethods.Like(row.Name, "test") in my query.

This performs a case-insensitive comparison.

Answer 5

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

Answer 6

I guess the best way to have a case sensitive search is to create a store procedure and perform the case sensitive search there it self. Below link provide details of how to handle the case sensitive query in SQL.

http://www.a2zmenu.com/MySql/Case-sensitive-search-in-SQL.aspx