Convert.ToInt32 could not be translated when argument is user-defined function

[LaXiS96]

Hello,
I am refactoring/rewriting an application which has been using EFCore since around 2.0 and stores Dictionary<string, object?> entity properties as JSON string columns in SQL Server.
The original implementation abused internal EFCore classes to rewrite dictionary accesses to JSON_VALUE SQL functions, among other things. I have now switched to EFCore 7.0, using value converters and interceptors to try and achieve the same (and more) functionality, but I'm getting stuck somewhere.
I cannot use the recently introduced JSON columns support because my dictionaries are dynamic and cannot be mapped to a static model.

I have a JsonDictionary class which extends Dictionary<string, object?> and implements equality interfaces and overrides, plus value converter and comparer for JSON serialization and change tracking. CRUD operations on the DbContext are working as expected.

My entity looks like this:

public class Entity {
    public JsonDictionary Dictionary { get; private set; }
}

I need to filter values within the Dictionary property in SQL, which is why I configured this user defined function for JSON_VALUE:

// Function method:
string? JsonValue(object propertyReference, string path)
    => throw new InvalidOperationException("This function call should have been translated to SQL!");

// Function configuration:
modelBuilder.HasDbFunction(JsonValue, builder =>
{
    builder.HasName("JSON_VALUE").IsBuiltIn();
    // https://github.com/dotnet/efcore/issues/28393#issuecomment-1181498610
    builder.HasParameter("propertyReference").HasStoreType("nvarchar(max)");
    builder.HasParameter("path");
});

I then have an IQueryExpressionInterceptor which replaces calls to dictionary indexers and get_Item with calls to JsonValue defined above.
For example, entity.Dictionary["key"] is translated to JsonValue(entity.Dictionary, $"$.\"key\"") within the LINQ expression tree.

Here is the issue I am having, this works as expected:

context.Set<Entity>.Where(s => s.Dictionary["test"] == (object)1234).ToList();

while this doesn't:

context.Set<Entity>.Where(s => Convert.ToInt32(s.Dictionary["test"]) == 1234).ToList();

The Expression DebugView after my interceptor in the former case is:

.Call System.Linq.Queryable.Where(
    .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
    '(.Lambda #Lambda1<System.Func`2[Entity,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[Entity,System.Boolean]>(Entity $s)
{
    .Call DbFunctionsExtensions.JsonValue($s.Properties, "$."test"") == (System.Object)1234
}

while for the latter it is:

.Call System.Linq.Queryable.Where(
    .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
    '(.Lambda #Lambda1<System.Func`2[Entity,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[Entity,System.Boolean]>(Entity $s)
{
    .Call System.Convert.ToInt32(.Call DbFunctionsExtensions.JsonValue($s.Properties, "$."test"")) == 1234
}

and this is the exception I get in this case:

System.InvalidOperationException: The LINQ expression 'DbSet<Entity>()
    .Where(s => Convert.ToInt32(DbFunctionsExtensions.JsonValue(
        propertyReference: s.Properties,
        path: "$."test"")) == 1234)' could not be translated. Additional information: Translation of method 'System.Convert.ToInt32' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', 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 Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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__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 Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ConsoleApp1.Program.Main(String[] args) in C:\Users\me\Source\Repos\ConsoleApp1\Program.cs:line 54
   at ConsoleApp1.Program.<Main>(String[] args)

I was expecting Convert.ToInt32(entity.Dictionary["key"]) == value to be translated to CONVERT(int, JSON_VALUE([Dictionary], '$."key"')) = @value or something along those lines.
Why does EFCore complain about not being able to translate Convert.ToInt32 calls (which should be supported by the SQL Server provider according to documentation)?
Is there something wrong with my database function setup?

Thank you for any insight you might have

Include provider and version information

EF Core version: 7.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer 7.0.5
Target framework: net6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.5.5

[LaXiS96]

I created a minimal project that reproduces the problem: https://github.com/LaXiS96/EFCoreIssue30818

[ajcvickers]

Note for triage: very similar to #28287.

[LaXiS96]

I did some more testing and updated my repro repository.
I added a string property to Entity, which I'm directly calling the JsonValue user-defined function on; surprisingly Convert.ToInt32 works in this configuration.

This is the QueryDebugView when querying on the new string property:

DbSet<Entity>()
    .Where(x => Convert.ToInt32(DbFunctionsExtensions.JsonValue(
        propertyReference: x.JsonString, 
        path: "$."key"")) == 6543)

and this is for the dictionary access (before my interceptor):

DbSet<Entity>()
    .Where(x => Convert.ToInt32(x.Dictionary.get_Item("key")) == 1234)

In the interceptor, this is the returned expression for the new string property:

.Call System.Linq.Queryable.Where(
    .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
    '(.Lambda #Lambda1<System.Func`2[EFCoreIssue30818.Entity,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[EFCoreIssue30818.Entity,System.Boolean]>(EFCoreIssue30818.Entity $x) {
    .Call System.Convert.ToInt32(.Call EFCoreIssue30818.DbFunctionsExtensions.JsonValue(
            $x.JsonString,
            "$."key"")) == 6543
}

and this is for the translated dictionary access:

.Call System.Linq.Queryable.Where(
    .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
    '(.Lambda #Lambda1<System.Func`2[EFCoreIssue30818.Entity,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[EFCoreIssue30818.Entity,System.Boolean]>(EFCoreIssue30818.Entity $x) {
    .Call System.Convert.ToInt32(.Call EFCoreIssue30818.DbFunctionsExtensions.JsonValue(
            $x.Dictionary,
            "$."key"")) == 1234
}

The only difference is in the property access expression that is passed as the first argument to JsonValue ($x.JsonString vs $x.Dictionary).

[LaXiS96]

I cloned EFCore's source and debugged the repro; the culprit is in the ToInt32 overload that is used.
When called on the string property, the overload is Int32 ToInt32(String), while in the other case it's Int32 ToInt32(Object), which is not supported by SqlServerConvertTranslator.

Is this behavior expected, as object is too generic to guarantee type safety?
Is there a workaround other than casting the dictionary access to string to use a supported overload?

Edit:
I just realized that I probably don't even need to convert the dictionary side to the expected type, since SQL Server is smart enough to coerce the constant side when it's a string:

[ajcvickers]

Note from triage: we should also translate the object method.

[imangd]

Hi @ajcvickers , I'd like to work on this issue if it's still open for contribution.
My understanding is that Convert.ToInt32(Object o) needs to be translated to CONVERT SQL Server function and if this is correct, I'm planning to add object to SupportedTypes in SqlServerConvertTranslator. Am I on the right track?

[roji]

@Ricketiman yes, that looks like the thing to do.