Case insensitive string compare in LINQ-to-SQL

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.

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 would be:

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

NOTE, HOWEVER that this does not work in this case! Therefore we are stuck with ToUpper or ToLower.

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://www.siao2.com/2007/10/01/5218976.aspx
• http://www.siao2.com/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"

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

This performs a case-insensitive comparison.

With .NET core, System.Data.Linq.SqlClient.SqlMethods is not available, use this instead

EF.Functions.Like(row.Name, "test")

According to the EF Core documentation, the decision not to provide an out of the box translation of case insensibility comparison is by design, mostly due to performance concerns since the DB index wouldn't be used:

> .NET provides overloads of string.Equals accepting a StringComparison enum, which allows specifying case-sensitivity and culture for the comparison. By design, EF Core refrains from translating these overloads to SQL, and attempting to use them will result in an exception. For one thing, EF Core does know not which case-sensitive or case-insensitive collation should be used. More importantly, applying a collation would in most cases prevent index usage, significantly impacting performance for a very basic and commonly-used .NET construct.

That being said, starting with EF Core 5.0, it's possible to specify a collation per query, which can be used to perform a case insensitive comparison:

Dim s = From row In context.Table 
        Where EF.Functions.Collate(row.Name, "SQL_Latin1_General_CP1_CI_AS") == "test"

and in C#

var s = context.Table
   .Where(row => EF.Functions.Collate(row.Name, "SQL_Latin1_General_CP1_CI_AS") == "test")

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.

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

I tried this using Lambda expression, and it worked.

List<MyList>.Any (x => (String.Equals(x.Name, name, StringComparison.OrdinalIgnoreCase)) && (x.Type == qbType) );

The following 2-stage approach works for me (VS2010, ASP.NET MVC3, SQL Server 2008, Linq to SQL):

result = entRepos.FindAllEntities()
	.Where(e => e.EntitySearchText.Contains(item));

if (caseSensitive)
{
	result = result
		.Where(e => e.EntitySearchText.IndexOf(item, System.StringComparison.CurrentCulture) >= 0);
}

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

Sometimes value stored in Database could contain spaces so running this could be fail

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

Solution to this problems is to remove space then convert its case then select like this

 return db.UsersTBs.Where(x => x.title.ToString().ToLower().Replace(" ",string.Empty).Equals(customname.ToLower())).FirstOrDefault();

Note in this case

customname is value to match with Database value

UsersTBs is class

title is the Database column

Remember that there is a difference between whether the query works and whether it works efficiently! A LINQ statement gets converted to T-SQL when the target of the statement is SQL Server, so you need to think about the T-SQL that would be produced.

Using String.Equals will most likely (I am guessing) bring back all of the rows from SQL Server and then do the comparison in .NET, because it is a .NET expression that cannot be translated into T-SQL.

In other words using an expression will increase your data access and remove your ability to make use of indexes. It will work on small tables and you won't notice the difference. On a large table it could perform very badly.

That's one of the problems that exists with LINQ; people no longer think about how the statements they write will be fulfilled.

In this case there isn't a way to do what you want without using an expression - not even in T-SQL. Therefore you may not be able to do this more efficiently. Even the T-SQL answer given above (using variables with collation) will most likely result in indexes being ignored, but if it is a big table then it is worth running the statement and looking at the execution plan to see if an index was used.