Efcore.pg: HasDbFunction does not support functions with upper-case letters

[ljani]

If I build a DbFunction, which name is in PascalCase (MyFunction), Npgsql is looking for the method in lowercase instead of PascalCase:

pgsql.PostgresException (0x80004005): 42883: function myfunction(boolean) does not exist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1219
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken) in C:\projects\EFCore.PG\src\EFCore.PG\Storage\Internal\NpgsqlExecutionStrategy.cs:line 72
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)

Here's the class I'm using for registering my DbFunctions

public static class MyDbFunctions
{
    public static ModelBuilder HasMyDbFunctions(this ModelBuilder builder)
    {
        var functions = GetType()
            .GetMethods(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static)
            .Where(method => method.GetCustomAttribute<DbFunctionAttribute>() != null);
        foreach (var function in functions)
        {
            builder.HasDbFunction(function, functionBuilder => functionBuilder.HasName(function.Name));
        }
        return builder;
    }

    [DbFunction]
    public static bool MyFunction(bool b)
    {
        return false;
    }
}

As a workaround I can manually quote the function name:

builder.HasDbFunction(function, functionBuilder => functionBuilder.HasName('"' + function.Name + '"'));

I'd say that only calling HasName explicitly should quote the identifier if needed, and only passing in a MethodInfo without HasName would still normalize the name.

This might be a regression from #327.

[roji]

This isn't related to #327: see https://github.com/aspnet/EntityFrameworkCore/issues/12757 for a long discussion on this.

In a nutshell, the problem is that for "system functions" we want the ability to use mixed case without quoting. For example, we want to be able call functions such as ST_AsGeometry() (from PostGIS). If you quote this the call will fail, because the actual function is st_asgeometry. Similarly, we want to be able to use COUNT() instead of count().

So ideally we want to have a concept of a system function vs. a user function, with the latter being registered via HasDbFunction(). User functions would get quoted if they contain upper-case characters, while system functions wouldn't. This currently does not exist in EF Core itself, but I plan on submitting a PR to fix it.

Unfortunately, in the meantime you have to either use all-lowercase letters (so quoting isn't necessary), or include the quotes yourself as you've done.

I'll keep this issue open to track the work needed on the PostgreSQL side after https://github.com/aspnet/EntityFrameworkCore/issues/12757 is done.

[roji]

This has been fixed upstream and functions with upper-case letters will be properly quoted in 3.0.0. Will do a PR to clean up the tests.