Unwanted conversion of latin letters in sql functions

Question

I'm using Ms-Sql DB and I have a field that is saved in the DB as nvarchar type. I use linq to sql in the following way:

var names = NamesTable.where(n=>n.name.Contains("acções"));

this query returns names containing: acçoes/ accoes or any other combination. it seems that sql doesn't recognize the latin special letters as different from the english ones.

How can I solve this problem?

Answer 1

In SqlServer you can set a "collation" per column. Usually (I think) it's "accent sensitive", but your case appears to be "accent insensitive". See what it says for the collation, if it ends on "AI", then it's indeed Accent Insensitive. You need an "AS" for "Accent Sensitive".

And further you could have a "CI" (for "Case Insensitive") or "CS" (for "Case Sensitive").