In linqPad filter by JSON is very simple and funy!

JUDAH

It's works with premium edition.

1) Open MyExtensions.cs file, Open nuget manager by CTRL+SHIFT+P, then install EntityFrameworkCore.SqlServer.
2) Paste the folowing code:

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

    public static void RegisterJsonValueFunctions(this ModelBuilder modelBuilder)
    {
        var jsonValueMethod = typeof(MyDbFunctions).GetMethod(nameof(MyDbFunctions.JsonValue));
        var stringTypeMapping = new StringTypeMapping("NVARCHAR(MAX)", DbType.String);

        modelBuilder
            .HasDbFunction(jsonValueMethod)
            .HasTranslation(args => new SqlFunctionExpression("JSON_VALUE", args, nullable: true, argumentsPropagateNullability: new[] { false, false }, jsonValueMethod.ReturnType, stringTypeMapping))
            .HasParameter("column").Metadata.TypeMapping = stringTypeMapping;
    }
}

3) fix all errors by import usings.

Now, this extension is global, and available for all your queries.

4) go to your query, and change the kanguage to c# program.

5) Override OnModelBuilding, and register the function.

Example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.RegisterJsonValueFunctions();
}

public void Main()
{
    Transactions
    .Where(t => MyDbFunctions.JsonValue(t.RawData, "$.YEARBUILT") == "XYZ")
    .Dump();
}

Enjoy!

JUDAH

stackoverflow: https://stackoverflow.com/questions/70297415