Linq to SQL case sensitivity causing problems

Question

I've seen this question, but that's asking for case-insensitive comparisons when the database is case-sensitive. I'm having a problem with the exact opposite.

I'm using SQL Server 2005, my database collation is set to Latin1_General_CI_AS.

I've got a table, "User", like this:

CREATE TABLE [dbo].[User] (
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED (
  [Id] ASC
 )
)

And I'm using the following code to populate it:

string[] names = new[] { "Bob", "bob", "BoB" };
using (MyDataContext dataContext = new AppCompatDataContext())
{
    foreach (var name in names)
    {
        string s = name;
        if (dataContext.Users.SingleOrDefault(u => u.Name == s) == null)
            dataContext.Users.InsertOnSubmit(new User { Name = name });
    }

    dataContext.SubmitChanges();
}

When I run this the first time, I end up with "Bob", "bob" and "BoB" in the table.

When I run it again, I get an InvalidOperationException: "Sequence contains more than one element", because the query against the database returns all 3 rows, and...

SELECT * FROM [User] WHERE Name = 'bob'

... is case-insensitive.

That is: when I'm inserting rows, Linq to SQL appears to use C# case-sensitive comparisons. When I query later, Linq to SQL uses SQL Server case-insensitive comparisons.

I'd like the initial insert to use case-insensitive comparisons, but when I change the code as follows...

if (dataContext.Users.SingleOrDefault(u => 
        u.Name.Equals(s, StringComparison.InvariantCultureIgnoreCase)
   ) == null)

... I get a NotSupportedException: "Method 'Boolean Equals(System.String, System.StringComparison)' has no supported translation to SQL."

Question: how do I get the initial insert to be case-insensitive or, more precisely, match the collation of the column in the database?

Update: This doesn't appear to be my problem. My problem appears to be that SingleOrDefault doesn't actually look at the pending inserts at all.

Answer 1

Try

u.Name.ToLower() == s.ToLower()

As this can translate to LOWER(name) = LOWER(@p1) in SQL. I'm not sure if LINQ is smart enough to do that though.

Answer 2

When your database does the case-insensitive match, it finds all the rows you need plus a few more. C# can do the case-sensitive match that you actually need. Therefore, retrieve all the case-insensitively-matched rows first, then filter them by the case-sensitive match:

var user = dataContext.Users
    .Where(u => u.Name == s)
    .AsEnumerable()
    .Where(u => u.Name.Equals(s, StringComparison.InvariantCultureIgnoreCase))
    .SingleOrDefault();

if (user == null)
    // ...