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.

Chaldea

Thanks! This really saved my ass today. Note: it works with other LINQ extensions too like LINQQuery.Contains("VaLuE", StringComparer.CurrentCultureIgnoreCase) and LINQQuery.Except(new string[]{"A VaLUE","AnOTher VaLUE"}, StringComparer.CurrentCultureIgnoreCase). Wahoo! – Alcatraz

Funny, I'd just read that ToUpper was better in comparisons from this source: msdn.microsoft.com/en-us/library/dd465121 – Lewak

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

Til

It seems this doesn't apply to SQL Server: print upper('Große Straße') returns GROßE STRAßE – Chaldea

Also, the sample code you provided has the same problem as the code I provided as far as being case-sensitive when run via LINQ-to-SQL on an MS SQL 2005 database. – Chaldea

I agree. Sorry I was unclear. The sample code I provided does not work with Linq2Sql as you pointed out in your original question. I was merely restating that the way you started was a great way to go -- if it only worked in this scenario. And yes, another Mike Kaplan soapbox is that SQL Server's character handling is all over the place. If you need case insensitive and can't get it any other way, I was suggesting (unclearly) that you store the data as Uppercase, and then query it as uppercase. – Til

...that is, do the up-case conversion in .NET and pass the transformed string to SQL both for storage and for query. – Til

Is there any problem with performing the UPPER within SQL server (Use ToUpper in .NET and let it translate to UPPER() in SQL) on both sides and comparing the result? I wouldn't want to have all data always appear in all uppercase just to enforce case-insensitive compares on a database that, to be honest, in all likelihood will be case-insensitive; I just want this logic in place in case it isn't. Also, in this scenario, is there any significant difference between UPPER and LOWER? (Any demonstrations you can provide?) – Chaldea

I think if you call UPPER() for SQL both for storing and querying then you're probably OK. I would shy away from LOWER since not all unicode characters have lower-case equivalents, although all characters have uppercase representations. I don't know an example of such a case, but Michael Kaplan talks about them. – Til

We're not calling ToUpper or ToLower for storing data because we don't want all data to always be displayed in upper-case, but want to retain mixed case data display. Is this a problem? – Chaldea

I don't understand the suggestion to use UPPER() for storing data. Why can't you just use Upper() when retrieving the data? – Chaldea

Well, if you have a case sensitive database, and you store in mixed case and search in Upper case, you won't get matches. If you upcase both the data and the query in your search, then you're converting all the text you're searching over for every query, which isn't performant. – Til

I'm seeing some interesting results from the execution plan. When I execute the statement "select * from OITM where Upper(ItemCode) = '23-RED'" I see the execution plan is doing an index scan, a key lookup and a nested loop, which for some reason takes only takes 14% as long as a clustered index scan (alone) that occurs when I execute the very similar "select * from OITM where Upper(ItemCode) = '19-BLACK'". – Chaldea

@BlueMonkMN, are you sure you pasted the correct snippets? It is hard to believe MSSQL Server prefers Red more than Black. – Trier

I couldn't explain it either. Maybe it's because of the length of the string reaching some threshold or maybe it's because of the statistics behind the different values in that particular table or maybe it's because 19-BLACK was value on the first row -- who knows. – Chaldea

I tried what u say, and I'm encountering this issue: #5081227 – Sustentacular

For anyone coming here from a search - the links in the post are dead, but can still be reached via archive.org: web.archive.org/web/20130723203412/http://blogs.msdn.com/b/… – Hexapla

Why is it marked as correct answer? This is not the answer to the question, it only confused a lot of people as this is not working for Linq2SQL. – Swithin

@AlbertoMontellano Why do you say it's not an answer to the question? The question asked is "Is ToLower or ToUpper the best option here? Is one better than the other?" My post responds that ToUpper is better. And explains a bunch of relevant data too. – Til

Hi @AndrewArnott, the context of the question is LINQ-to-SQL , and your proposal of the best way to do a case-insensitive equality check doesn't work with this. I think the question is about how to compare in LINQ-to-SQL, no matter what other ways exists outside this. – Swithin

@Chaldea why did you accept this answer? I thought it is a right answer for Linq-to-Sql. It is absolutely a wrong wrong wrong answer. It doesn't work with LINQ to SQL. If you are still think it is a right answer, please, change your question so it doesn't appear on google search. Thanks.... – Formula

@Formula There is no right answer except the one proposed in the question (ToUppet) and that's what this answer confirmed, if you read the first 3 comments you will understand. This is the only answer that confirmed what we wish and what we are stuck with. – Chaldea

@Chaldea In that case, Andrew should change his answer. Because this is wrong. Or you should post your own answer and mark it as answer. When I clicked on google search, people are immediately look at the accepted answer. I doubt anyone look at the comment until the code crashes because of the wrong answer. It is absolutely misleading. Perhaps, (in the most humble way - and I beg of you) is to change your question so it won't appear on google search. Right now, it is on top of the google search in which the answer is absolutely, utterly, undeniably wrong... – Formula

@Formula it might look like a wrong answer to someone whose first language is not English (or who doesn't read carefully), but if you read closely, the statement says "Ideally, the best way to do a case-insensitive equality check is:" etc..., which implies that this is not the actual answer. But I agree this can be misleading. I will edit the answer to try to make it more obvious. Let me know if the updated answer (give me 5 minutes) looks better. – Chaldea

@Chaldea well, you were asking Case insensitive string compare in LINQ-to-SQL. It doesn't need a native English speaker to understand that the question is about string compare in LINQ-to-SQL. Thanks for editing the answer. But I'm still hoping it does not highlight String.Equals(row.Name, "test", StringComparison.OrdinalIgnoreCase) because it will NOT work in LINQ-to-SQL – Formula

Answer 2

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

This performs a case-insensitive comparison.

Bran

ha! been using linq 2 sql for several years now but hadn't seen SqlMethods until now, thanks! – Loquacious

Brilliant! Could use more detail, though. Is this one of the expected uses of Like? Are there possible inputs that would cause a false positive result? Or a false negative result? The documentation on this method is lacking, where's the documentation that will describe the operation of the Like method? – Paranoia

I think it just relies on how SQL Server compares the strings, which is probably configurable somewhere. – Bran

System.Data.Linq.SqlClient.SqlMethods.Like(row.Name, "test") is the same as row.Name.Contains("test"). As Andrew is saying, this depends on sql server's collation. So Like (or contains) doesn't always perform a case-insensitive comparison. – Crawley

Be aware, this make the code too couple to SqlClient. – Engel

The equivalent when working with EF Core is EF.Functions.Like(entity.Name, "value") – Overstock

Answer 3

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

Howard

Answer 4

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

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

Despain

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

Cryobiology

That's the issue. Normally the field I use is case sensitive (the chemical formula CO [carbon monoxide] is different from Co [cobalt]). However, in a specific situation (search) I want co to match both Co and CO. Defining an additional property with a different "server data type" is not legal (linq to sql only allows one property per sql column). So still no go. – Crawley

Also, if doing Unit Testing, this approach won't likely be compatabile with a data mock. Best to use the linq/lambda approach in the accepted answer. – Kuibyshev

Answer 6

I tried this using Lambda expression, and it worked.

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

Meddlesome

That's because you're using a List<>, which means the comparison takes place in-memory (C# code) rather than an IQueryable (or ObjectQuery) which would perform the comparison in the database. – Toxicant

What @Toxicant said. This answer is simply wrong, considering that the context is linq2sql, and not regular linq. – Latty

Answer 7

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);
}

Misapprehension

This code has a bug if the text starts with the search text (should be >= 0) – Whitnell

@FlatlinerDOA it should actually be != -1 because IndexOf "returns -1 if the character or string is not found" – Toxicant

Answer 8

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

Phinney

What is the SQL text into which this gets translated, and what allows it to be case insensitive in an SQL environment that would otherwise treat it as case-sensitive? – Chaldea

Answer 9

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

Flytrap

Answer 10

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.

Azide

I don't understand what you're saying. 1) Strings themselves can't be case-insensitive or case-sensitive in .NET, so I can't pass a "case-insensitive string". 2) A LINQ query basically IS a lambda expression, and that's how I'm passing my two strings, so this doesn't make any sense to me. – Chaldea

I want to perform a CASE-INSENSITIVE comparison on a CASE-SENSITIVE database. – Chaldea

What CASE-SENSITIVE database are you using? – Azide

Also, a LINQ query is not a lambda expression. A LINQ query is composed of several parts (most notably query operators and lambda expressions). – Azide

This answer doesn't make sense as BlueMonkMN comments. – Horselaugh

Answer 11

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.

Kloman

That's not true (it doesn't cause the rows to be returned to the client). I've used String.Equals and the reason it doesn't work is because it gets converted into a TSQL string comparison, whose behavior depends on the collation of the database or server. I for one do consider how every LINQ to SQL expression I write would be converted into TSQL. The way to to what I want is to use ToUpper to force the generated TSQL to use UPPER. Then all the conversion and comparison logic is still done in TSQL so you don't lose much performance. – Chaldea