60946

Case insensitive string compare with linq-to-sql and linq-to-objects

Question:

see also <a href="https://stackoverflow.com/questions/1718195/differences-between-linq-to-objects-and-linq-to-sql-queries" rel="nofollow">Differences between LINQ to Objects and LINQ to SQL queries</a>

We are using the <strong>some</strong> queries over our <strong>database</strong> and our <strong>in memory objects</strong>.

What is the best way of doing an insensitive string compare with linq-to-sql so that?

<ul><li>It runs fast on SQL Server</li> <li>The same query expression can be used with linq-to-objects to get the same result</li> </ul>

Using

a.ToLowerInvariant() == b.ToLowerInvariant()

at least gets the same results, but it does not get processed on SQL server as far as I can tell, so can be a lot slower then

a == b

Answer1:

I'm not an expert in linq to sql, but you could just use the ToUpperInvariant() method of string before compareing.

Answer2:

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 - <strong>CI</strong> stands for case insensitive and <strong>AS</strong> 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.

Answer3:

Here are the list of supported string operations in LINQ to SQL, which would obviously work in LINQ to objects: <a href="http://msdn.microsoft.com/en-us/library/bb882672.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/bb882672.aspx</a>

I personally haven't evaluated each for performance, but ToLower and ToUpper aren't supported in LINQ to SQL, so it seems like Compare is a good candidate. You can explore the translated SQL by using a tool like LINQPad, which has a free version and translates a query into database SQL, and see how it runs against the DB.

Also, LINQ to Objects probably differs so what's good for one may not be good for the other...

Recommend

  • Does concurrency in Scala happen automatically?
  • Random card generation
  • Combining element-wise and matrix multiplication with multi-dimensional arrays in NumPy
  • why stop() function doesn't work correctly?
  • Why performance of MySQL queries are so bad when using a CHAR/VARCHAR index?
  • Notepad++ Custom Function List (Basic)
  • Suppressing warning: comparison is always false due to limited scope of data type
  • what happens to the js code inside EM_ASM?
  • How to make Selenium WebDriver not to wait for whole page to load
  • Simple Way of NOT reading last N lines of a file in Python
  • Find rows with duplicate values across a small set of columns in R
  • Bitset as the return value of a function
  • Why should “miter” joints be slower than others?
  • Python: why regular expression is slower than replace() method?
  • How to prevent Internet Explorer Connection Timeout?
  • Performance issues when using iterators?
  • Webdriver Xpath Performance
  • Why are Pickle files in Pickle protocol 4 twice as large as those in protocol 3 without having any g
  • Regex to match charset
  • OpenMP for dependent variables
  • C# helper classes to implement NTLM authorization
  • What are zone turns?
  • Is it possible to run an application built on sql server 2008 to run with 2005
  • Classic ASP URL Rewriting
  • JSON encode and decode on PHP
  • Fail:(TESTMODE) Transactions of this market type cannot be processed on this system
  • Is playing sound in Javascript performance heavy?
  • Insert into database using onclick function
  • Resize panoramic image to fixed size
  • Volusion's generic SQL folder, functionality
  • Knitr HTML Loop - Some HTML output, some R output
  • Trying to switch camera back to front but getting exception
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • When should I choose bucket sort over other sorting algorithms?
  • Calling of Constructors in a Java
  • Free memory of cv::Mat loaded using FileStorage API
  • How do you join a server to an Active Directory (domain)?
  • Angular 2 constructor injection vs direct access
  • coudnt use logback because of log4j
  • Programmatically clearing map cache