Translate method of IMethodCallTranslator interface not called when method to translate is inside Where clause

[franklbt]

I have an issue with my query, it use ValueConverters and MethodTranslator. I try to filter Articles entity by a predicate on title, and the value is a JSON dictionary in db, in which each key is a lang.

Here is the (simplified) code:

    public class Program
    {
        public static void Main(string[] args)
        {
            IWebHostBuilder webHostBuilder = CreateWebHostBuilder(args);
            IWebHost webHost = webHostBuilder.Build();
            webHost.Run();
        }

        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseKestrel(options => options.Limits.MaxRequestBodySize = null) 
                .UseIIS()
                .CaptureStartupErrors(true)
                .UseStartup<Startup>();
    }

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            string connectionString = Configuration.GetConnectionString("DefaultConnection");
            services.AddDbContextPool<EntitiesDb>(options =>
            {
                options.UseSqlServer(connectionString, sqlServerBuilder =>
                {
                    sqlServerBuilder.UseCustomTypes();
                    sqlServerBuilder.UseNetTopologySuite();
                });
            });
            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env, GlobalConfiguration config)
        {
            app.UseMvc();
        }
    }

    public class EntitiesDb : DbContext
    {
        public readonly string InstanceName = Guid.NewGuid().ToString();

        public EntitiesDb(DbContextOptions<EntitiesDb> options) : base(options)
        {
        }

        public DbSet<Article> Articles { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Article>().ToTable("Articles", "cms");

            Expression<Func<string, Dictionary<string, string>>> convertFromProviderExpression = v =>
                JsonConvert.DeserializeObject<Dictionary<string, string>>(v,
                    new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore});

            Expression<Func<string, Dictionary<string, string>>> customConvertFromProviderExpression = v =>
                JsonExtensions.IsValidJsonObject(v)
                    ? JsonConvert.DeserializeObject<Dictionary<string, string>>(v,
                        new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore})
                    : new Dictionary<string, string> {{"fr-fr", v}};
            modelBuilder.Entity<Article>()
                .Property(e => e.Title)
                .HasConversion(convertToProviderExpression, customConvertFromProviderExpression);

            base.OnModelCreating(modelBuilder);
        }
    }

    public class Article
    {
        public Article()
        {
            this.CreationDate = DateTimeOffset.UtcNow;
            this.LatestModificationDate = DateTimeOffset.UtcNow;
        }

        [Key] public int Id { get; set; }
        public DateTimeOffset CreationDate { get; set; }
        public Guid? CreationUserId { get; set; }
        public DateTimeOffset LatestModificationDate { get; set; }
        public Guid? LatestModificationUserId { get; set; }

        public string DefaultLanguage { get; set; }
        public Dictionary<string, string> Title { get; set; }
    }

    [Route("api/articles")]
    [IsLoggedInApi]
    public class ArticlesController : BaseApiController
    {
        private readonly TranslationsHelper _translationsHelper;
        private readonly EntitiesDb _entitiesDb;

        public ArticlesController(TranslationsHelper translationsHelper,
            EntitiesDb entitiesDb)
        {
            _translationsHelper = translationsHelper;
            _entitiesDb = entitiesDb;
        }

        [HttpPost("blog")]
        public async Task<IActionResult> GetBlogArticles([FromBody] string title)
        {
            var articlesQuery = _entitiesDb.Articles
                .AsQueryable();

            if (!string.IsNullOrEmpty(model.Title))
            {
                var tradKeys = _translationsHelper.GetAvailableLanguages();
                var expressions = tradKeys
                    .Select(x => (Expression<Func<Article, bool>>) (y => EF.Functions.Like(y.Title[x], "%" + title + "%")))
                    .ToArray();
                articlesQuery = articlesQuery.Where(ExpressionHelper.MergeOrElseExpression(expressions));
            }

            var articlesCount = await articlesQuery.CountAsync();

            var articles = await articlesQuery
                .Select(x => new ArticleListModel
                {
                    Id = x.Id,
                    Title = x.Title,
                })
                .Skip((model.PageNumber - 1) * model.PageSize)
                .Take(model.PageSize)
                .ToArrayAsync();

            return Ok(articles);
        }

    }
        public class ArticleListModel
        { 
            public int Id { get; set; } 
            public Dictionary<string, string> Title { get; set; } 
        }

    public static class ExpressionHelper
    {

        public static Expression<Func<T, bool>> MergeOrElseExpression<T>(
            Expression<Func<T, bool>> left,
            Expression<Func<T, bool>> right)
        {
            var visitor = new SwapVisitor(left.Parameters[0], right.Parameters[0]);
            return Expression.Lambda<Func<T, bool>>(Expression.OrElse(
                    visitor.Visit(left.Body), right.Body), right.Parameters);
        }

        public static Expression<Func<T, bool>> MergeOrElseExpression<T>(
            params Expression<Func<T, bool>>[] expressions)
            => expressions.Aggregate(MergeOrElseExpression);
    }

    public class SqlServerDictionaryMethodCallTranslator : IMethodCallTranslator
    {
        public SqlExpression Translate(SqlExpression instance, MethodInfo method,
            IReadOnlyList<SqlExpression> arguments, IDiagnosticsLogger<DbLoggerCategory.Query> logger)
        {
            if (method.DeclaringType != typeof(Dictionary<string, string>)
                && method.DeclaringType != typeof(Dictionary<string, object>))
            {
                return null;
            }

            var methodName = method.Name;

            if (methodName != "get_Item" || instance is not ColumnExpression expression) return null;

            var relationalTypeMapping = arguments[0].TypeMapping;
            return new SqlFunctionExpression(
                "JSON_VALUE",
                new SqlExpression[]
                {
                    expression,
                    new SqlBinaryExpression(
                        ExpressionType.Add,
                        new SqlConstantExpression(Expression.Constant("lax $."), relationalTypeMapping),
                        new SqlBinaryExpression(
                            ExpressionType.Add,
                            new SqlConstantExpression(Expression.Constant("\""), relationalTypeMapping),
                            new SqlBinaryExpression(
                                ExpressionType.Add,
                                arguments[0],
                                new SqlConstantExpression(Expression.Constant("\""), relationalTypeMapping),
                                typeof(string),
                                relationalTypeMapping
                            ),
                            typeof(string),
                            relationalTypeMapping
                        ),
                        typeof(string),
                        relationalTypeMapping
                    )
                },
                true,
                new[] {true, false},
                typeof(string),
                relationalTypeMapping
            );
        }
    }

    public class SqlServerCustomMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
    {
        public IEnumerable<IMethodCallTranslator> Translators => new IMethodCallTranslator[]
        {
            new SqlServerDictionaryMethodCallTranslator()
        };
    }

 public class SqlServerDbContextOptionsExtension : IDbContextOptionsExtension
    {
        private DbContextOptionsExtensionInfo _info;
        public void ApplyServices(IServiceCollection services)
        {
            if (services == null) throw new ArgumentNullException(nameof(services));

            new EntityFrameworkRelationalServicesBuilder(services)
                .TryAddProviderSpecificServices(
                    x => x.TryAddSingletonEnumerable<IMethodCallTranslatorPlugin, SqlServerCustomMethodCallTranslatorPlugin>()
                        .TryAddSingletonEnumerable<IMemberTranslatorPlugin, SqlServerCustomMemberTranslatorPlugin>());
        }

        public void Validate(IDbContextOptions options)
        {
        }

        public DbContextOptionsExtensionInfo Info => _info ??= new ExtensionInfo(this);

        public class ExtensionInfo : DbContextOptionsExtensionInfo
        {
            public ExtensionInfo(SqlServerDbContextOptionsExtension sqlServerDbContextOptionsExtension) 
                : base(sqlServerDbContextOptionsExtension)
            {
            }

            public override bool IsDatabaseProvider
                => false;

            public override long GetServiceProviderHashCode()
                => 0;

            public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)
                => debugInfo["SqlServer:" + nameof(SqlServerCustomDbContextOptionsBuilderExtensions.UseCustomTypes)] = "1";

            public override string LogFragment
                => "using DictionarySupport ";
        }
    }
    public static class SqlServerCustomDbContextOptionsBuilderExtensions
    {
        public static object UseCustomTypes(this SqlServerDbContextOptionsBuilder optionsBuilder)
        {
            if (optionsBuilder == null) throw new ArgumentNullException(nameof(optionsBuilder));

            var coreOptionsBuilder = ((IRelationalDbContextOptionsBuilderInfrastructure)optionsBuilder).OptionsBuilder;

            var extension = coreOptionsBuilder.Options.FindExtension<SqlServerDbContextOptionsExtension>()
                            ?? new SqlServerDbContextOptionsExtension();

            ((IDbContextOptionsBuilderInfrastructure)coreOptionsBuilder).AddOrUpdateExtension(extension);

            return optionsBuilder;
        }
    }

When I run this, I have the following exception:

Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: The LINQ expression 'DbSet<Article>()
          .Where(a => __Functions_1
              .Like(
                  matchExpression: a.Title.get_Item("fr-fr"),
                  pattern: __p_2) || __Functions_1
              .Like(
                  matchExpression: a.Title.get_Item("fr-ca"),
                  pattern: __p_2) || __Functions_1
              .Like(
                  matchExpression: a.Title.get_Item("en-gb"),
                  pattern: __p_2))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToL
ist', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
         at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
         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__DisplayClass12_0`1.<ExecuteAsync>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at API.ArticlesController.GetBlogArticles(CompanyBlogArticlesRequest model) in C:\Travail\We Recruit\WeRecruit\Controllers\API\ArticlesController.cs:line 138
         at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isComplete
d)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
         at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.<Invoke>g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)

When I put a breakpoint at the start of the Translate method, it is never hit.

Include provider and version information

EF Core version: 5.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 5.0 Operating system: Windows IDE: JetBrain Rider

[ajcvickers]

/cc @smitpatel

[smitpatel]

Duplicate of #23410 Fixed in 5.0.1 @franklbt - Can you try on 5.0.1 patch release and verify if this works for you?

[franklbt]

The method is now called, but the value of arguments[0].TypeMapping inside SqlServerDictionaryMethodCallTranslator class is null. Is this behaviour normal ?

[smitpatel]

Given arguments[0] is verified to be columnExpression, it must have a type mapping associated with it which comes from model itself. Can you share a runnable repro project which demonstrate the behavior so we can investigate?