Linq to SQL case sensitivity causing problems
- by Roger Lipscombe
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.
