Ignoring accents in SQL Server using LINQ to SQL

How can I ignore accents (like ยด, `, ~) in queries made to a SQL Server database using LINQ to SQL?

UPDATE:

Still haven't figured out how to do it in LINQ (or even if it's possible) but I managed to change the database to solve this issue. Just had to change the collation on the fields I wanted to search on. The collation I had was:

SQL_Latin1_General_CP1_CI_AS

The CI stans for "Case Insensitive" and AS for "Accent Sensitive". Just had to change the AS to AI to make it "Accent Insensitive". The SQL statement is this:

ALTER TABLE table_name ALTER COLUMN column_name column_type COLLATE collation_type

Answers


In SQL queries (Sql Server 2000+, as I recall), you do this by doing something like select MyString, MyId from MyTable where MyString collate Latin1_General_CI_AI ='aaaa'.

I'm not sure if this is possible in Linq, but someone more cozy with Linq can probably translate.

If you are ok with sorting and select/where queries ALWAYS ignoring accents, you can alter the table to specify the same collation on the field(s) with which you are concerned.


See the following answer:

LINQ Where Ignore Accentuation and Case

Basically you need to alter the field type in SQL Server, e.g.

ALTER TABLE People ALTER COLUMN Name [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AI

There does not seem to be a way to do this using LINQ, apart from calling a custom method to remove diacritics (which would not be performant).


Need Your Help

jQuery .ajax() call to host equal to current page failing

jquery ajax

I'm building an interface to a backend system using JavaScript (with Jquery).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.