Ignoring accents in SQL Server using LINQ to SQL

Question

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

The purpose of tags isn't to just make anything up, the subject is linq, so just use 'linq', don't create 'sqltolinq' because nobody else will ever use such a random tag again, so it's a waste. — TravisO, Nov 26 '08 at 23:10
Hhmm, I didn't just create a new tag, I used 'linqtosql' that has been used in about 290 questions... — Farinha, Nov 26 '08 at 23:37
Thanks, I was just to apply regex replace. That would have been terrible! — David Lay, Nov 02 '09 at 20:48
If I change the collation of database to SQL_Latin1_General_CP1_CI_AS, I loose for example c with acute (U+0107) accent in SELECT query, this is a bad side effect. — Tomas Kubes, Nov 07 '16 at 07:46

JasonTrue · Accepted Answer · 2008-11-28T07:58:37.527

3

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.

edited Nov 28 '08 at 07:58

answered Nov 26 '08 at 23:06

JasonTrue

19,244
4
34
61

Here's a sample, I probably picked this up from one of my web searches but can't remember the URL. SELECT * FROM nametable WHERE name = 'abbee' COLLATE Latin1_General_CI_AI -- Case-Insensitive, Accent-Insensitive comparison AND LIKE '%Accent%' COLLATE Latin1_General_CI_AS -- Case-Insensitive, Accent-Sensitive comparison ORDER BY DESC COLLATE Latin1_General_CS_AS -- Case-Sensitive, Accent-Sensitive sort order – Raj Sep 24 '10 at 11:29

score 2 · Answer 2 · edited May 23 '17 at 12:06

2

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).

edited May 23 '17 at 12:06

Community

1
1

answered Nov 16 '11 at 00:21

Dunc

18,404
6
86
103

score 1 · Answer 3 · answered Nov 29 '10 at 05:39

1

LINQ to SQL doesn't have any specific functionality for setting the collation used for a query and so it will always use the database default.

answered Nov 29 '10 at 05:39

DamienG

6,575
27
43

score 1 · Answer 4 · answered Feb 01 '12 at 14:16

It seems that there is a way to ignore the collation differences in Linq to SQL by using t-sql functions:

CREATE FUNCTION [dbo].[func_ConcatWithoutCollation]
(
    @param1 varchar(2000),
    @param2 varchar(2000)
)
RETURNS varchar(4000)
AS
BEGIN
    IF (@param1 IS NULL) SET @param1 = ''
    IF (@param2 IS NULL) SET @param2 = ''
    RETURN @param1 COLLATE Latin1_General_CS_AS + @param2 COLLATE Latin1_General_CS_AS
END

to get this function in linq to sql, there is a switch for SqlMetal: /functions. Example:

"%ProgramFiles%\Microsoft SDKs\Windows\v7.0A\Bin\SqlMetal.exe" /server:. /database:NameOfDatabase /pluralize /code:ContextGenerated.cs /sprocs /views /functions

Use this function in Linq to sql like this:

from s in context.Services
where context.Func_ConcatWithoutCollation(s.Description, s.Email) == "whatever"
select s

It helped me, maybe somebody finds this useful too.

score 0 · Answer 5 · answered Oct 21 '21 at 12:47

A solution could be create an SQL Function to remove the diacritics, by applying to the input string the collation SQL_Latin1_General_CP1253_CI_AI, like so:

CREATE FUNCTION [dbo].[RemoveDiacritics] (
@input varchar(max)
)   RETURNS varchar(max)

AS BEGIN
DECLARE @result VARCHAR(max);

select @result = @input collate SQL_Latin1_General_CP1253_CI_AI

return @result
END

Then you could add it in the DB context (in this case ApplicationDbContext) by mapping it with the attribute DbFunction:

public class ApplicationDbContext : IdentityDbContext<CustomIdentityUser>
    {
        [DbFunction("RemoveDiacritics", "dbo")]
        public static string RemoveDiacritics(string input)
        {
            throw new NotImplementedException("This method can only be used with LINQ.");
        }

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
}

And finally use it in LINQ query, for example (linq-to-entities):

var query = await db.Users.Where(a => ApplicationDbContext.RemoveDiacritics(a.Name).Contains(ApplicationDbContext.RemoveDiacritics(filter))).tolListAsync();

Ignoring accents in SQL Server using LINQ to SQL

5 Answers5

Linked