EF.Functions.Like and Npgsql.EntityFrameworkCore.PostgreSQL

A story about wasted time due to errors in the documentation of the EntityFrameworkCore provider for PostgreSQL and my own gaps in knowledge.

The task was set. Find all records where in the test field the first letter is A, and the second character is any digit, and the sixth character is either 0 or 1.

It is most rational in this case to use the LIKE construct.

The top-level EFCore documentation says that we also need to use the DbFunctionsExtensions.Like method. Implementation of the SQL LIKE operation. In relational databases, this usually translates directly to SQL. https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbfunctionsextensions.like?view=efcore-7.0

public static bool Like (this Microsoft.EntityFrameworkCore.DbFunctions _, string matchExpression, string pattern);
...
The pattern which may involve wildcards %,_,[,],^.

Based on this information, our expression will look like this:

var result = _context.Vehicle.Where(vehicle => EF.Functions.Like(vehicle.СarNumber,"A[0,1,2,3,4,5,6,7,8,9]____[0,1]_"));

Let me remind you the conditions "the first character is A, the second is any digit, the sixth is 0 or 1"

This will work if our database is MSSQL, and the SQL dialect is T-SQL.

The problem will arise when our database is free PostgreSQL (Microsoft, it's time to make MS SQL as Open Source!).

PostgreSQL adheres to kosher old-school SQL92 notation.

What patterns Like supports in different databases is well written on Stackoverflow: https://stackoverflow.com/questions/712580/list-of-special-characters-for-sql-like-clause

The problem is that the official documentation on the implementation of the EntityFrameworkCore provider for PostgreSQL is misleading, repeating that it implements the same pattern as the standard EFCore implementation https://www.npgsql.org/efcore/api/Microsoft.EntityFrameworkCore.NpgsqlDbFunctionsExtensions.html#Microsoft_EntityFrameworkCore_NpgsqlDbFunctionsExtensions_ILike_DbFunctions_System_String_SystemString

String    pattern    
The pattern which may involve wildcards %,_,[,],^.

I should note here that the documentation from Microsoft is more truthful, as it warns in advance who the implementation of the Like mechanism depends on the specific database provider. And the documentation information is given for the MS SQL reference provider.

If we look at the PostreSql documentation, we see that Like supports only two patterns according to SQL92 - these are only % and _.

If we want to use more complex conditions, then for this we need to use the construction " SIMILAR TO" https://postgrespro.com/docs/postgresql/9.6/functions-matching which supports partial use of regular expression rules. SQL regular expressions are a curious cross between LIKE notation and common regular expression notation.. Unfortunately this instruction is not supported by the current PostreSQL provider.

Summary. If you are using the EntityFramworkCore ORM and your target database is PostreSQL, use only the % or _ control characters as a pattern for the Like statement. Or, contribute to the PostreSQL EntityFrameworkCore provider to support T-SQL statements by using SIMILAR TO.