How can I perform a case- and accent- insensitive LIKE (substring) query using PredicateBuilder, EF Core 5, and Postgresql 10+?

Question

In SQL Server, it is relatively straight forward to create a case-insensitive, accent-insensitive, LIKE query using EF Core 5 and PredicateBuilder.

For example:

• The user is is trying to find a customer in the database using a standard North American keyboard (i.e. accents are kind of a pain to type).
• Searching for helene should match Mme. Hélène Laurendeau. It is important we can match a substring and not just a prefix or suffix.

Assuming we have an entity Customer with property Name, the following snippet will work as expected:

var predicate = PredicateBuilder
                 .New<Customer>(c => EF.Functions.Collate(c.Name, 
                                                          "Latin1_general_CI_AI")
                                                 .Contains("helen"));

var customers = context.Customers.AsExpandable().Where(predicate);

How we can we perform the same query in PostgreSQL (10 or higher)?

We just did a quick (basically 1:1) migration from SQL Server and are trying to update our code to make sure the general migration is possible. This question does not take possible query speed issues into account (see links for more info there.)

Answer 1

Assuming your newly-created Postgres database is UTF8 (which I believe it is by default)

1. Ensure unaccent is installed. If not, CREATE EXTENSION unaccent;
2. Update your predicate as follows:

var predicate = PredicateBuilder
                  .New<Customer>(c => EF.Functions.ILike(EF.Functions.Unaccent(c.Name),
                                                        "%helen%"));

3. Profit! :)

Notes: It is possible to collate answers from various questions on SO and other sources to reach this conclusion, but I've been unable to find one place where it makes sense post a comment with this solution that could be easily located. Also, I don't believe that introducing full-text search is the answer to this question, but happy to update it if I'm mistaken.

This answer is by far the best and most complete one I've seen and highly encourage people to dig into it when they have time. If you are trying to move from SQL Server over to Postgres as a prototype, though, this answer should save you a lot of pain and time :)

Some answers have suggested creating an index on a lowercase / unaccent-ed version of the column but that does not directly work since unaccent is not IMMUTABLE. You can wrap these as required and then index them (examples in links.)

Also, attempting to pass a collation directly via EF.Functions (after having created it on the server) does not appear to work either, but perhaps I am something missing about Postgres there.

It may be possible to tweak individual column collations.

Some relevant questions include:

• PostgreSQL accent + case insensitive search
• Query PostgreSQL with Npgsql and Entity Framework using unaccent
• Does PostgreSQL support "accent insensitive" collations?

Other sources:

• https://github.com/dotnet/efcore/issues/8813
• https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity
• http://www.albahari.com/nutshell/predicatebuilder.aspx