By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

We have a project that supports mssql, mariadb and PostgreSql.

In our c# code we use like. We want to change everywhere that we have LIKE to ILIKE so it can work as we want in PostgreSql. If we do this we see that in other dadabases it loads all rows in memory and then executes in c# the operation. It works, but not in sql level.

So, can you execute like when the providers aren't PostgreSql?

Or otherwise, is there a way EF.Functions.Like executing ILIKE in PostgreSql?

If you want to perform case-insensitive searches/comparisons, you may want to take a look at the PostgreSQL CITEXT type for your columns. This type had numerous issues in previous versions of EF Core, but thanks to version 3.0's type inference it will work much better. Note that switching to this means that all comparisons will be insensitive, not just LIKE.

Otherwise there is currently no way to make EF.Functions.Like translate to ILIKE - the EF Core provider simply translates to LIKE, which on PostgreSQL is case-sensitive like everything else. In general EF Core doesn't exactly aim to make your code behave in an identical way across database.

/cc @divega , we had this discussion some time ago.

From PostgreSQL v12 on, they allow you to use case- and accent-insensitive collations.

This would be the solution...

I mean that if the configured database provider is not postgresql, it should use the origin DbFunctions.Like, There is not point for ILIKE in other providers, and also there is not point in our code to write if is postgre configured, this query else other query.

@bramve-fenetre that's unfortunately not going to be possible - as written above, ILIKE is a PG-specific function, and the SQL Server provider isn't going to recognize its .NET function.

You could implement an expression tree visitor which identifies calls to EF.Functions.ILike and replaces them with EF.Functions.Like when on SQL Server, though that's not completely trivial to do.