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.