linq search for French characters

Question

I'm usint EF and have a simple LINQ statement and want to search for words. So there is Textbox search and submit button.

when searchtext contains "march" it finds eg. "des marchés", but If I search for "marché" it doesn't find. So it's the French character.

listAgendaItems = dc.agenda.Where(a =>
                        a.libelle_activite.Contains(searchText)
).ToList<agenda>();

The database and the table Agenda have extended properties -> Collation : French_CI_AS

So how can I makes sure I get the French words, too? like "é, à" etc

I also tried to search for "marche" but it doesn't find "marchés".

Answer 1

Your collation French_CI_AS is "Case-Insensitive", "Accent-Sensitive". If you want a query for "marches" to match "marchés", you need French_CI_AI as your collation. In most languages, that's actually NOT what native speakers want, because the accents are semantically important, but that may depend on the circumstances or context.

If, in fact, your users do always want accent insensitive searches, you should set that collation property to AI instead of AS on the table (or the specific fields). Otherwise, if the need is rare, you can apply collation to a table in MS Sql on a per-query basis; keep in mind that if there is no index on that collation there may be a substantial performance cost. That may be nearly immaterial when you're doing a %wildcard% query, however, since you'll generally have a full table scan in that case anyway.

The last I checked, it wasn't possible to specify a collation in a Linq query directly, so if you are doing case insensitivity on an ad-hoc basis, you'd need to use direct-to-sql query through your data context.

Edited: Based on your comment, it sounds like you are allowing HTML content to be stored in your database. You have numeric character references in your table, which SQL Server knows nothing about, since they're a feature of HTML, XML and SGML. You can only make this searchable if those characters are string literals in a suitable encoding.

NVARCHAR will store the content in Unicode, specifically UTF-16, and VARCHAR will use Windows-1252 with French collation.

If you are accepting this input via web forms, make sure the page encoding is appropriate. If you are only supporting modern browsers (essentially anything IE5+), UTF-8 is well supported, so you should consider using UTF-8 for all of your requests and responses.

Make sure in your web.config, you have something like this:

<configuration>
   <system.web>
      <globalization
         requestEncoding="utf-8"
         responseEncoding="utf-8" />
   </system.web>
</configuration>

If you already have data stored with those numeric character references in your database, you can unescape them by translating &#ddddd; into literal UTF-16 sequences, and store them again. Make sure you don't accidentally unescape semantically important NCRs like the greater-than, less-than or ampersand codepoints.