Efcore: How to use multiple IMethodCallTranslatorProvider

[thefat32]

Ask a question

Hello, we are using Oracle provider for EF Core 3.1 and would like to add our custom IMethodCallTranslatorProvider so we can extend more functions to be translated as SQL.

Adding our translator through

        void IDbContextOptionsExtension.ApplyServices(IServiceCollection services)
        {
            services.AddSingleton<IMethodCallTranslatorProvider, CustomMethodCallTranslatorPlugin>();
        }

makes EF completly ignore Oracle's OracleMethodCallTranslatorProvider

Is there a way to register another provider or custom translators to be used alongside Oracle's ?

Right now our solution is to male our coustom provider to extend OracleMethodCallTranslatorProvider, but this is not ideal as this class is internal and could change on a future Oracle SQL Proovider update

Include your code

• DbContextOptionsBuilder Extension

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

            return optionsBuilder;
        }

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

• IDbContextOptionsExtension

    public class DbContextOptionsExtension : IDbContextOptionsExtension
    {
        private DbContextOptionsExtensionInfo _info;

        public void Validate(IDbContextOptions options)
        {
        }

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

        void IDbContextOptionsExtension.ApplyServices(IServiceCollection services)
        {
            services.AddSingleton<IMethodCallTranslatorProvider, CustomMethodCallTranslatorPlugin>();
        }

        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 long GetServiceProviderHashCode()
            {
                return 0;
            }
        }
    }

• IMethodCallTranslatorProvider (now extending OracleMethodCallTranslatorProvider)

    public sealed class CustomMethodCallTranslatorPlugin : OracleMethodCallTranslatorProvider
    {
        public CustomMethodCallTranslatorPlugin(RelationalMethodCallTranslatorProviderDependencies dependencies)
            : base(dependencies)
        {
            ISqlExpressionFactory expressionFactory = dependencies.SqlExpressionFactory;
            this.AddTranslators(new List<IMethodCallTranslator>
            {
                new ConvertToIntTranslator(expressionFactory),
                new StringSubStringTranslator(expressionFactory)
            });
        }
    }

[smitpatel]

Use true plugin infrastructure. Rather than replacing service of IMethodCallTranslatorProvider, add a service implementation for IMethodCallTranslatorPlugin. See https://github.com/dotnet/efcore/blob/main/src/EFCore.Sqlite.NTS/Query/Internal/SqliteNetTopologySuiteMethodCallTranslatorPlugin.cs for example.

[thefat32]

Perfect! Just extending IMethodCallTranslatorPlugin worked like a charm!

    public class CustomMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
    {
        public CustomMethodCallTranslatorPlugin(ISqlExpressionFactory sqlExpressionFactory)
        {
            Translators = new IMethodCallTranslator[]
            {
                new ConvertToIntTranslator(sqlExpressionFactory),
                new StringSubStringTranslator(sqlExpressionFactory)
            };
        }

        public virtual IEnumerable<IMethodCallTranslator> Translators { get; }
    }

and adding the service with

        void IDbContextOptionsExtension.ApplyServices(IServiceCollection services)
        {
            services.AddSingleton<IMethodCallTranslatorPlugin, CustomMethodCallTranslatorPlugin>();
        }

Thank you very much!

[roji]

If you're looking for simple additional function translations, there's also the approach of using DbFunction, which may be lighter/more appropriate. The docs for that are still in progress (https://github.com/dotnet/EntityFramework.Docs/issues/500), but it entails invoking modelBuilder.HasDbFunction in your model's OnModelCreating and configuring things there.

[thefat32]

I've also seen this approach. I think for now is better for us to use IMethodCallTranslatorPlugin. It let us organize our code better and is pretty straight foward to add a new translator once the extensions are coded.

Thanks for your input!