How can I use SQL Server JSON_VALUE function in EF 6 Code First for classic .NET

Question

How can I use SQL Server JSON_VALUE function in EF 6 Code First for classic .NET? I found I can do it in EF Core like this:

public static class JsonExtensions
{
    public static string JsonValue(string column, [NotParameterized] string path)
    {
        throw new NotSupportedException();
    }
}

// In OnModelCreating
modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
    .HasName("JSON_VALUE")
    .HasSchema("");

// And then the usage
var result = db.Blogs.Select(t => JsonExtensions.JsonValue(t.Log, "$.JsonPropertyName")).ToArray();

But how can I achieve this in EF 6 in classic .NET (in my case, its 4.6.1)?

score 8 · Accepted Answer · answered May 23 '18 at 14:14

In classic .NET it's a little bit different, but still possible like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new RegisterJsonValueFunctionConvention());
}

// Than define your function
[DbFunction("CodeFirstDatabaseSchema", "JSON_VALUE")]
public static string JsonValue(string expression, string path)
{
    throw new NotSupportedException();
}

Then, because JSON_VALUE is not defined in the Entity Framework SQL Server provider manifest, you have to create IStoreModelConvention like this:

public class RegisterJsonValueFunctionConvention : IStoreModelConvention<EdmModel>
{
    public void Apply(EdmModel item, DbModel model)
    {
        var expressionParameter = FunctionParameter.Create("expression", GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
        var pathParameter = FunctionParameter.Create("path", GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
        var returnValue = FunctionParameter.Create("result", GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.ReturnValue);
        CreateAndAddFunction(item, "JSON_VALUE", new[] { expressionParameter, pathParameter }, new[] { returnValue });
    }

    protected EdmFunction CreateAndAddFunction(EdmModel item, string name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues)
    {
        var payload = new EdmFunctionPayload { StoreFunctionName = name, Parameters = parameters, ReturnParameters = returnValues, Schema =  GetDefaultSchema(item), IsBuiltIn = true };
        var function = EdmFunction.Create(name, GetDefaultNamespace(item), item.DataSpace, payload, null);
        item.AddItem(function);
        return function;
    }

    protected EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
    {
        return model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType;
    }

    protected string GetDefaultNamespace(EdmModel layerModel)
    {
        return layerModel.GlobalItems.OfType<EdmType>().Select(t => t.NamespaceName).Distinct().Single();
    }

    protected string GetDefaultSchema(EdmModel layerModel)
    {
        return layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault();
    }
}

This isn't about .NET Core or "classic" .NET. EF Core can run on both runtimes — Panagiotis Kanavos, May 23 '18 at 14:19
Can you show an example of how are you using this? I'm having trouble setting the expression parameter value. — yosbel, Oct 10 '18 at 16:43
Have you managed to solve your problem? Sorry, I have no more example code and DB, would have to create it from scratch. — Jonatan Dragon, Oct 16 '18 at 09:34
@walkerbox has spelled it all out perfectly. Example on how to use it. Assumptions: The static method `JsonValue` is in some sort of global namespace. The column containing JSON is named "Json" in your EF model. `context.MyTable.Select(row => JsonValue(row.Json, "$.MyJsonSelector")` — Aaron Hudon, Oct 30 '18 at 00:50
@walkerbox thank you so much for the detailed steps. But I am running into problem where I need to convert (or cast) string to different data type like for e.g., BIT. How can I achieve CAST(JSON_VALUE(json, "$.MyJsonSelector") as bit)? Thank you in advance for pointing me in some direction to achieve this. — sam, Jul 02 '19 at 18:53

score 2 · Answer 2 · answered Sep 22 '18 at 18:24

2

I'm not sure it works with Entity-Framework 6, but probably worth mentioning Impatient.

It Allows you to perform LINQ queries on json_value columns and more.

answered Sep 22 '18 at 18:24

Shimmy Weitzhandler

101,809
122
424
632

2

I don't suppose you have an example. I've been sifting through the docs and tests and am struggling to find one – farlee2121 Mar 18 '20 at 18:44

How can I use SQL Server JSON_VALUE function in EF 6 Code First for classic .NET

2 Answers2

Linked