CODEWITHSUNDEEP
c#sql-serverentity-frameworksql-server-2008-r2entity-framework-6
mipe34
mipe34

Reputation: 5666

Accent insensitive search using EF6 and SQL Server 2008 R2 - Czech language

How to do accent insensitive search query using SQL Server 2008 R2 and EF 6?

I need to do accent insensitive search on let's say user.name column using the Entity framework 6. I have tried to change the collation on column from default Czech_CI_AS to Czech_CI_AI. But it does not work for some Czech letters with wedges like Č, Ř, Š, Ž because the collation treats them as different letters :

http://collation-charts.org/mssql/mssql.0405.1250.Czech_CI_AI.html

I have found similar question here:

How do I perform an accent insensitive compare in SQL Server for 1250 codepage

But the proposed solution using collation Czech_100_CI_AI does not work either (for those special letters).

I have also found few sources how to do it in plain T-SQL. Like this:

SELECT * 
FROM [dbo].[User] 
WHERE name LIKE '%c%' COLLATE Latin1_General_CI_AI

It works fine. But I do not want to use plain SQL queries. I would like to manage it in an EF way.

Upvotes: 1

Views: 676

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37500

In Entity Framework, when you use Contains() method in where() extension method in IQueryable, it is translated to where clause with like operator in SQL. So I guess this is what are you looking for. You can refer to this SO question.

Upvotes: 1

mipe34
mipe34

Reputation: 5666

I have end up with this solution:

Create view with two columns - one for the search, second for presentation (latin collation will remove some accents from the result).

CREATE VIEW [dbo].[v_UserSearch]
AS
SELECT    
           dbo.[User].name AS FirstName,
           dbo.[User].name COLLATE Latin1_General_CI_AI AS FirstNameCI
FROM       dbo.[User]

Create DB mapping for the view in EF context.

Use the FirstNameCI column for the search in EF.

if (!string.IsNullOrWhiteSpace(filter.FirstName))
   query = query.Where(x => x.c.FirstNameCI.StartsWith(filter.FirstName));

Use the FirstName column for presentation.

Upvotes: 4

Related Questions