Expression tree to SQL with EF Core

Question

We have a column where JSON data is stored as a string. This JSON data is read and converted through materialization to an IDictionary<string, object>. This all works fine until I want to filter on it. The filtering is only applied after getting the data from the database. We will have millions of records so this is not acceptable. My filter is being completely ignored as a WHERE clause by EF Core obviously since probably it has no idea how to parse the MethodCallExpressions.

I'm looking for a way to get as close as possible to the SQL query I have below with the expression tree I have.

I need to convert this:

.Call System.Linq.Queryable.Where(
    .Constant<QueryTranslator`1[Setting]>(QueryTranslator`1[Setting]),
    '(.Lambda #Lambda1<System.Func`2[Setting,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[Setting,System.Boolean]>(Setting $$it)
{
    ((System.Nullable`1[System.Int32]).If (
        $$it.Value != null && .Call ($$it.Value).ContainsKey("Name")
    ) {
        ($$it.Value).Item["Name"]
    } .Else {
        null
    } > (System.Nullable`1[System.Int32]).Constant<Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]>(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)
    == .Constant<System.Nullable`1[System.Boolean]>(True)
}

Into this:

SELECT *
FROM [Setting]
WHERE JSON_VALUE([Value], 'lax $.Name') > 1; -- [Value_Name] > 1 is also fine

With an ExpressionVisitor I've succeeded in getting as close as WHERE [Value] = 'Something' but this only works for strings and the key name is lacking.

Answer 1

Until it get "official" support, you can map the JSON_VALUE using the EF Core 2.0 introduced Database scalar function mapping.

For instance, add the following static method inside your context derived class or in separate static class as below:

public static class MyDbFunctions
{
    [DbFunction("JSON_VALUE", "")]
    public static string JsonValue(string source, string path) => throw new NotSupportedException();
}

and if it is in separate class, add the following to your context OnModelCreating override (not needed if the method is in the context):

modelBuilder.HasDbFunction(() => MyDbFunctions.JsonValue(default(string), default(string)));

Now you can use it inside your LINQ to Entities queries similar to EF.Functions. Just please note that the function returns string, so in order to trick the compiler to "cast" it to numeric, you can use the double cast technique shown below (tested and working in EF Core 2.1.2):

var query = db.Set<Setting>()
    .Where(s => (int)(object)MyDbFunctions.JsonValue(s.Value, "lax $.Name") > 1);

which translates to the desired

WHERE JSON_VALUE([Value], 'lax $.Name') > 1

Another (probably type safer) way to perform the conversion is to use Convert class methods (surprisingly supported by SqlServer EF Core provider):

var query = db.Set<Setting>()
    .Where(s => Convert.ToInt32(MyDbFunctions.JsonValue(s.Value, "lax $.Name")) > 1);

which translates to

WHERE CONVERT(int, JSON_VALUE([Value], 'lax $.Name')) > 1

Answer 2

There is the breaking change in Entity Framework Core 3.X. DbFunction.Schema being null or empty string configures it to be in model's default schema

And only with that example in the link I've been able to add DBFunction to our project.

Functions inside MyDbContext:

[DbFunction("JSON_VALUE", "dbo")]
public static string JsonValue(string source, string path) => throw new NotSupportedException();

[DbFunction("JSON_QUERY", "dbo")]
public static string JsonQuery(string source, string path) => throw new NotSupportedException();

Setup:

modelBuilder
.HasDbFunction(typeof(MyDbContext).GetMethod(nameof(MyDbContext.JsonQuery)))
.HasTranslation(args => SqlFunctionExpression.Create("JSON_QUERY", args, typeof(string), null));
    
modelBuilder
.HasDbFunction(typeof(MyDbContext).GetMethod(nameof(MyDbContext.JsonValue)))
.HasTranslation(args => SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null));

Use (simplified):

var query = from sometable in _context.SomeEntity
            where MyDbContext.JsonValue(sometable.Data, "$.PrimaryKey.Id") == somevalue
            orderby sometable.Date descending
            select new SomeModel
            {
                SomeJsonArray = MyDbContext.JsonQuery(sometable.Data, "$.Changes")
            };