Expression tree to SQL with EF Core

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")
            };

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

Tags:

C#

Entity Framework Core

Recent Posts