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

User · Answer

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

User · Answer

I tried this using Lambda expression  and it worked   List lt MyList gt  Any  x   gt   String Equals x Name  name  StringComparison OrdinalIgnoreCase    amp  amp   x Type    qbType

User · Answer

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

User · Answer

I used System Data Linq SqlClient SqlMethods Like row Name   test   in my query   This performs a case-insensitive comparison

User · Answer

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

User · Answer

The following 2-stage approach works for me  VS2010  ASP NET MVC3  SQL Server 2008  Linq to SQL    result   entRepos FindAllEntities        Where e   gt  e EntitySearchText Contains item     if  caseSensitive        result   result          Where e   gt  e EntitySearchText IndexOf item  System StringComparison CurrentCulture   gt   0

User · Answer

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     foo bar com  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  foo bar com    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

User · Answer

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   gt  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

User · Answer

where row name StartsWith q  true  System Globalization CultureInfo CurrentCulture

[.net] Case insensitive string compare in LINQ-to-SQL

Examples related to .net

Examples related to vb.net

Examples related to linq