Case insensitive string compare in LINQ-to-SQL

Solution 1:

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"

Solution 2:

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

This performs a case-insensitive comparison.

Solution 3:

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

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

Solution 4:

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")