1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mipe34
  • 5,596
  • 3
  • 26
  • 38

2 Answers2

3

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.

mipe34
  • 5,596
  • 3
  • 26
  • 38
1

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.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • 1
    I think you misunderstood the question. from what I understand, the problem is the accent-sensitivity of the search, not how to perform a search using EF... – Zohar Peled Feb 20 '18 at 15:55
  • Well, OP stated that query with `LIKE` operator works perfectly, so I pointed, how he can achieve that query :) – Michał Turczyn Feb 20 '18 at 15:56
  • Yes, with the `collate`... I don't know if EF lets you specify that. – Zohar Peled Feb 20 '18 at 15:59
  • @Michal Turczyn: Yes, but it is not because of `LIKE` operator but because of Latin collation. `StartsWith` is also partially translated to `LIKE` statement. – mipe34 Feb 20 '18 at 16:01