Why do I get the wrong result when comparing UTF16 strings in a linq-to-sql select?

I am using C# and .Net 4.0 with MS SQL 2008开发者_StackOverflow中文版.

I am running an integration test to verify that data is getting correctly stored and retrieved. It fails more often than not. When I look into it I see that I am getting the wrong value back from the linq-to-sql call. I have profiled the linq-to-sql statement and discovered that in Server Management Studio, the profiled SQL returns the wrong value, while a hand typed query with the same parameters works correctly.

The linq-to-sql query and result:

exec sp_executesql N'SELECT TOP (1) [t0].[ID], [t0].[UserName], [t0].TCID
FROM [dbo].[Users] AS [t0]
WHERE ([t0].[TCID] = @p0) AND ([t0].[UserName] = @p1)',N'@p0 int,@p1
nvarchar(4000)',@p0=8,@p1=N'ҭРӱґѻ'

Results in

ID        UserName    TCID
2535      ҭРґѻӱ       8

As you can see, UserName does not match what was in the equality check.

If I do this, I get the expected result:

SELECT TOP 1000 [ID]
    ,[UserName]
    ,[TCID]
FROM [dbo].[Users]
where TCID=8 and username = 'ҭРӱґѻ'

I get back:

ID        UserName    TCID

Which is correct.

UserName is nvarchar(50), ID and TCID are int.

Any ideas why the first query gets the wrong result?

You're not getting results on the second query because you forgot to prefix the parameter with N. I bet you get a result just like with the dynamic SQL if you use:

SELECT TOP 1000 [ID]
    ,[UserName]
    ,[TCID]
FROM [dbo].[Users]
where TCID=8 and username = N'ҭРӱґѻ'; -- note the N prefix here

Now, I'm not saying you should get a result, but that should make the behavior consistent between your two testing methods. What is the collation of the column? You can "fix" this in a way by specifying a binary collation. For example, this should yield proper behavior:

SELECT COUNT(*) 
  FROM [dbo].[Users]
  WHERE [UserName] = N'ҭРӱґѻ' COLLATE Latin1_General_BIN;

-- 0

SELECT COUNT(*) 
  FROM [dbo].[Users]
  WHERE [UserName] = N'ҭРґѻӱ' COLLATE Latin1_General_BIN;

-- 1

With the collation you are using (probably a SQL Server-specific collation), some Unicode code points are not defined. Thus SQL Server treats them as if they were an empty string:

SELECT CASE WHEN N'ӱ' COLLATE SQL_Latin1_General_CP1_CI_AS = N'' THEN 'YES' ELSE 'NO' END

If we use a newer Windows collation such as Cyrillic_General_100_CI_AS, we see that these strings do not match:

SELECT CASE WHEN N'ӱ' COLLATE Cyrillic_General_100_CI_AS = N'' THEN 'YES' ELSE 'NO' END

Here's a blog post on MSDN that should explain more.