Upgrade from .NET 5 to .NET 6 - Using a custom IMethodCallTranslator no longer works

[pafrench]

I've injected my own IMethodCallTranslatorProvider to effectively monkey patch in PostGIS methods in that aren't currently available via Npgsql. This was working as expected in .NET 5, but after upgrading to .NET 6 I get the following exception thrown:

System.InvalidCastException: Unable to cast object of type 'Imagery.Warehouse.Metadata.Data.EFExtensions.ExtraNpgsqlMethodCallTranslatorProvider' to type 'Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlMethodCallTranslatorProvider'.
   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor..ctor(RelationalSqlTranslatingExpressionVisitorDependencies dependencies, QueryCompilationContext queryCompilationContext, QueryableMethodTranslatingExpressionVisitor queryableMethodTranslatingExpressionVisitor)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitorFactory.Create(QueryCompilationContext queryCompilationContext, QueryableMethodTranslatingExpressionVisitor queryableMethodTranslatingExpressionVisitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor..ctor(QueryableMethodTranslatingExpressionVisitorDependencies dependencies, RelationalQueryableMethodTranslatingExpressionVisitorDependencies relationalDependencies, QueryCompilationContext queryCompilationContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalQueryableMethodTranslatingExpressionVisitorFactory.Create(QueryCompilationContext queryCompilationContext)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)
   at Imagery.Warehouse.Metadata.Data.Stores.ReadStore.GetImageset(String imagesetUrn) in /home/paul.french/src/imagery-warehouse-metadata/lib/Imagery.Warehouse.Metadata.Data/Stores/ReadStore.cs:line 57

Here's a brief overview of the implementing code:

public static class NpgsqlDbContextOptionsBuilderExtensions
{
    public static DbContextOptionsBuilder UseExtraFunctions(this DbContextOptionsBuilder optionsBuilder)
    {
        var extension = GetOrCreateExtension(optionsBuilder);
        ((IDbContextOptionsBuilderInfrastructure)optionsBuilder).AddOrUpdateExtension(extension);

        return optionsBuilder;
    }
    
    private static NpgsqlDbContextOptionsExtension GetOrCreateExtension(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.Options.FindExtension<NpgsqlDbContextOptionsExtension>()
           ?? new NpgsqlDbContextOptionsExtension();
}

public class NpgsqlDbContextOptionsExtension : IDbContextOptionsExtension
{
    private DbContextOptionsExtensionInfo _info;

    public void Validate(IDbContextOptions options)
    {
        // Nothing to do
    }

    public DbContextOptionsExtensionInfo Info {
        get
        {
            return _info ??= new MyDbContextOptionsExtensionInfo(this);
        }
    }

    public void ApplyServices(IServiceCollection services)
    {
        // services.AddSingleton<IMethodCallTranslatorProvider, ExtraNpgsqlMethodCallTranslatorProvider>();
        services.AddScoped<IMethodCallTranslatorProvider, ExtraNpgsqlMethodCallTranslatorProvider>();
    }

    private sealed class MyDbContextOptionsExtensionInfo : DbContextOptionsExtensionInfo
    {
        public MyDbContextOptionsExtensionInfo(IDbContextOptionsExtension instance) : base(instance) { }

        public override bool IsDatabaseProvider => false;

        public override string LogFragment => "";

        public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)
        {
        }

        public override int GetServiceProviderHashCode() => 0;

        public override bool ShouldUseSameServiceProvider(DbContextOptionsExtensionInfo other) => false;
    }
}

public sealed class ExtraNpgsqlMethodCallTranslatorProvider : RelationalMethodCallTranslatorProvider
{
    public ExtraNpgsqlMethodCallTranslatorProvider(RelationalMethodCallTranslatorProviderDependencies dependencies) : base(dependencies)
    {
        var expressionFactory = dependencies.SqlExpressionFactory;
        AddTranslators(new List<IMethodCallTranslator>
        {
            new ExtraNpgsqlMethodCallTranslator(expressionFactory)
        });
    }
}

public static class ExtraNpgsqlDbFunctionsExtensions
{
    public static Geometry Force3D(this DbFunctions _, Geometry geometry) => throw new NotSupportedException();

    public static Geometry Union(this DbFunctions _, Geometry geometry) => throw new NotSupportedException();
    
    public static Geometry Multi(this DbFunctions _, Geometry geometry) => throw new NotSupportedException();
}

public class ExtraNpgsqlMethodCallTranslator : IMethodCallTranslator
{
    private readonly ISqlExpressionFactory _expressionFactory;

    private static readonly MethodInfo _force3DMethod
        = typeof(ExtraNpgsqlDbFunctionsExtensions).GetMethod(
            nameof(ExtraNpgsqlDbFunctionsExtensions.Force3D),
            new [] { typeof(DbFunctions), typeof(Geometry)});

    private static readonly MethodInfo _unionMethod
        = typeof(ExtraNpgsqlDbFunctionsExtensions).GetMethod(
            nameof(ExtraNpgsqlDbFunctionsExtensions.Union),
            new [] { typeof(DbFunctions), typeof(Geometry)});
    
    private static readonly MethodInfo _multiMethod
        = typeof(ExtraNpgsqlDbFunctionsExtensions).GetMethod(
            nameof(ExtraNpgsqlDbFunctionsExtensions.Multi),
            new [] { typeof(DbFunctions), typeof(Geometry)});

    public ExtraNpgsqlMethodCallTranslator(ISqlExpressionFactory expressionFactory)
    {
        _expressionFactory = expressionFactory;
    }

    public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments, IDiagnosticsLogger<DbLoggerCategory.Query> logger)
    {
        if (method == _force3DMethod)
        {
            return _expressionFactory.Function(
                "ST_Force3D",
                new[] {arguments[1]},
                nullable: true,
                new[] {true},
                method.ReturnType,
                arguments[1].TypeMapping);
        }

        if (method == _unionMethod)
        {
            return _expressionFactory.Function(
                "ST_Union",
                new[] { arguments[1] },
                true,
                new[] { true },
                method.ReturnType,
                arguments[1].TypeMapping);
        }
        
        if (method == _multiMethod)
        {
            return _expressionFactory.Function(
                "ST_Multi",
                new[] { arguments[1] },
                true,
                new[] { true },
                method.ReturnType,
                arguments[1].TypeMapping);
        }

        return null;
    }
}

Which is then registered/called in Startup.cs:

services.AddDbContext<ReadOnlyMetadataDbContext>((provider, builder) =>
{
    builder.UseNpgsql(_configuration.GetConnectionString(Data.Connection.Constants.ConnectionStringNames.Reader), o =>
    {
        o.UseNetTopologySuite();
        var databasePasswordManager = provider.GetRequiredService<IDatabasePasswordManager>();
        o.ProvidePasswordCallback(databasePasswordManager.GetReaderPassword);
    });
    builder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    builder.UseExtraFunctions();
    if (isSecretManagerEnabled)
    {
        builder.AddInterceptors(provider.GetRequiredService<ReaderConnectionInterceptor>());
    }
});

[roji]

@pafrench I can take a look at the above, but is there a reason you're replacing IMethodCallTranslatorProvider rather than simply using user-defined function mapping? This feature is there precisely to allow users to add function translations which aren't included in the provider; IMethodCallTranslatorProvider isn't really meant for user overriding (although that does work too).

Better yet, if you want to submit a PR with additional PostGIS translations, that would be very welcome.

[pafrench]

Hi @roji.

Previously I have submitted a PR to add a function translation (was ST_Force2D), but at the time we were on .NET 5 & had to wait until .NET 6 was released until we were able to use (you yourself said that only bug fixes are released on the current branch/version, with enhancements going on the next/future release).

If you could take a look at the above that would be great (least amount of change required as we already have something implemented in .NET 5).

Otherwise once I get back to work (Jan 10th) I can take a look at the user-defined function mapping (assuming it provides the same functionality as what I've currently got, as we're also creating expression trees using operations such as EF.Functions.Force2D, EF.Functions.Transform etc).

[roji]

@pafrench looking at your code above, your custom translator ExtraNpgsqlMethodCallTranslatorProvider doesn't seem to extend Npgsql's built-in NpgsqlMethodCallTranslatorProvider - this is what the exception is saying pretty clearly. Unless I've misunderstood your code, that means that when your translation provider is used, all other Npgsql translations are disabled, which doesn't sound so good. You can simply have ExtraNpgsqlMethodCallTranslatorProvider extend NpgsqlMethodCallTranslatorProvider, adding your specific custom translator - this should both fix the exception and allow access to the standard Npgsql translations as well.

However, the above does seem a bit over-complicated. User-defined function mappings should provide a much easier way to add translations (if you hit a wall you can let me know). And as an aside, if you want to replace an internal service such as IMethodCallTranslatorProvider (which again, shouldn't be necessary in this case), you can simply use ReplaceService to do that directly:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseNpgsql(@"Host=localhost;Username=test;Password=test")
        .ReplaceService<IMethodCallTranslatorProvider, ExtraNpgsqlMethodCallTranslatorProvider>();

[pafrench]

@roji FYI I've gone with the option of simply extending NpgsqlMethodCallTranslatorProvider (along with the ReplaceService call), IE:

namespace Imagery.Warehouse.Metadata.Data.EFExtensions
{
    /// <summary>
    /// We're simply extending Npgsql's existing provider so that we can add our own custom translator
    /// as per https://github.com/npgsql/efcore.pg/issues/2186#issuecomment-1002519164
    /// </summary>
#pragma warning disable EF1001
    public sealed class ExtraNpgsqlMethodCallTranslatorProvider : NpgsqlMethodCallTranslatorProvider
    {
        public ExtraNpgsqlMethodCallTranslatorProvider(
            RelationalMethodCallTranslatorProviderDependencies dependencies,
            IModel model,
            INpgsqlOptions npgsqlOptions)
            : base(dependencies, model, npgsqlOptions)
#pragma warning restore EF1001
        {
            var expressionFactory = dependencies.SqlExpressionFactory;
            AddTranslators(new List<IMethodCallTranslator>
            {
                new ExtraNpgsqlMethodCallTranslator(expressionFactory)
            });
        }
    }
}

This was chosen as the change has the least amount of impact. I've also disabled the following warning:

Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlMethodCallTranslatorProvider is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release.

Do I have to be worried about this? How likely is it that it'll be altered at some point?

[roji]

Pretty unlikely - there may be some minor API changes to react to in new major versions. It's internal since the proper way to add translations is via user-defined translations as I wrote above.

I don't think there's anything specific to do here on the Npgsql side, so I'll go ahead and close this - but don't hesitate to post back if you have any questions.

[WolfgangKluge]

I still think/hope it's a bug (I ran into the same issue). The problem seems to be that there's only a single reference to an IMethodCallTranslatorProvider inside RelationalSqlTranslatingExpressionVisitorDependencies where I'd expect an enumerable or a composition.

In my case I cannot use user-defined function mapping since I need access to the IModel (and ISqlExpressionFactory). Would be great to get that inside the much simpler HasTranslation()-Api, but currently that seems not to be possible.

[roji]

@WolfgangKluge there's intentionally only one IMethodCallTranslatorProvider; as I wrote above, you're expected to extend the provider's implementation and add your own translations.

[WolfgangKluge]

@roji that would not work with multiple extensions (that are not aware of each other).

For now I've created a composite provider that can handle multiple instances from the service provider. That works pretty well but I'm a bit afraid of the next versions.

[roji]

@WolfgangKluge if you're looking to create a reusable plugin, EF Core has an extensibility API that allows you to register additional IMethodCallTranslatorProviders; see for example the SQL Server spatial plugin for an example. This allows you to package a plugin as a nuget package, which, when added to a context configuration, adds more translators (and other features).

[WolfgangKluge]

@roji I'll look into that, thanks

[WolfgangKluge]

@roji IMethodCallTranslatorPlugin works great and doesn't feel like a hack anymore. Thanks again.
One last question - is there any documentation on this topic (what kind of plugins are there, what could you achieve, ...)? I cannot find much and nothing official.

[roji]

Unfortunately not... Writing EF Core providers and plugins is a topic we do need to document, but there's never enough time for it... In the meantime, I can only suggest looking at existing plugins and at the EF source code to understand what's possible...