Linq to Sql case insensitive equality

Question

I am reading contradictory explanations on Linq to Sql string comparisons.

When I do the following:

  from p in db.People 
  where p.UserName=username
  select p

username="jOHn"

I get the correct case insensitive result. Is Linq doing this by default or is this happening in SQL database?

Answer 1

I think you get conflicting results based on what your db variable points to and where the compare is actually executed. If it can, linq will build the query and send it to the SQL server. It seems like you could force case insensitive by calling

where p.UserName.ToLower()=username.ToLower()

Answer 2

Your sample query will translate to something roughly like this:

select [t0].col1, [t0].col2, ..., [t0].coln from [schema].[People] where [t0].UserName = @p0

...the value in the username variable will be passed in the @p0 sql variable. As such, case sensitivity, accent sensitivity etc is controlled by the collation you have set up your SQL Server instance/db/table/column to use. If not specified anywhere else, the DBs or DB instance's default collation is used, but collation can be specified all the way down to the column level.

Most people run SQL Server with case insensitive (CI) collations but as I have said above, it can be overridden in the DB so you just need to check what collation you have there.

This is in contrast to if you do the same thing as a L2O (linq to objects) query, in that case case-sensitivity is the default and you would have to make it case insensitive by using the string.equals override that allow you to specify culture and/or case insensitivity...