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:
He says "String.ToUpper – Use ToUpper rather than ToLower, and specify InvariantCulture in order to pick up OS casing rules"
That's because you are using LINQ To Entities which is ultimately convert your Lambda expressions into SQL statements. That means the case sensitivity is at the mercy of your SQL Server which by default has SQL_Latin1_General_CP1_CI_AS Collation and that is NOT case sensitive.
Using ObjectQuery.ToTraceString to see the generated SQL query that has been actually submitted to SQL Server reveals the mystery:
string sqlQuery = ((ObjectQuery)context.Thingies
.Where(t => t.Name == "ThingamaBob")).ToTraceString();
When you create a LINQ to Entities query, LINQ to Entities leverages the LINQ parser to begin processing the query and converts it into a LINQ expression tree. The LINQ expression tree is then passed to Object Services API, which converts the expression tree to a command tree. It is then sent to the store provider (e.g. SqlClient), which convert the command tree into the native database command text. Query get executed on the data store and the results are Materialized into Entity Objects by Object Services. No logic has been put in between to take case sensitivity into account. So no matter what case you put in your predicate, it will always treat as the same by your SQL Server unless you change your SQL Server Collates for that column.
Server side solution:
Therefore, the best solution would be to change the collation of the Name column in the Thingies table to COLLATE Latin1_General_CS_AS which is case sensitive by running this on your SQL Server:
ALTER TABLE Thingies
ALTER COLUMN Name VARCHAR(25)
COLLATE Latin1_General_CS_AS
For more information on the SQL Server Collates, take a a look at SQL SERVER Collate Case Sensitive SQL Query Search
Client-side solution:
The only solution that you can apply on client side is to use LINQ to Objects to do yet another comparison which doesn't seem to be very elegant:
Thingies.Where(t => t.Name == "ThingamaBob")
.AsEnumerable()
.First(t => t.Name == "ThingamaBob");
Best Answer
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.