If the database provider is something else from PostgreSql ILIKE should do like

[xrkolovos]

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?

[roji]

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.

[xrkolovos]

what about ILIKE translating to LIKE in other systems except postgresql?

[roji]

ILIKE is a PostgreSQL-specific concept that doesn't exist anywhere else - the C# function doesn't even exist in other providers...

[roji]

Am going to close this as there's nothing actionable here currently, but we can continue the conversation and possibly reopen again.

[xrkolovos]

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.

[xrkolovos]

PostgreSQL v12 they say late 2019

[bramve-fenetre]

Having the exact same problem here. Have to write IF ELSE for every query using ILIKE...
I would also really like it if ILIKE internally just gets translated to a LIKE for MSSQL

[roji]

@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.