Search code examples
entity-framework-6npgsql

Using "~*" in Postgres using Entity Framework 6

I'm trying to use the case-insensitive regex operator ~* of Postgres using Entity Framework 6.

When using Regex.IsMacht(column, pattern, RegexOptions.IgnoreCase) this translates into ~ (as described in the mapping page https://www.npgsql.org/efcore/mapping/translations.html)... and this does result in a case-insensitive filter, using ~* also ignores diacritics so this would result in better results for our users.

Is it possible to create a query with ~* using EF6?

edit: Here is a screenshot of a query as a reaction to the remark by Shay Rojansky.

enter image description here

Solution

  • The answer here is that there's no behavioral difference between the PostgreSQL ~* operator and Npgsql's current way of doing case-insensitive matches (?i)...; both are equivalent.

    Note that I've merged this PR for version 8.0 which improves various aspects of the SQL generated around regexes, and also switches to ~* (but for unrelated readability reasons).