I am attempting to utilize SQL Server’s built in JSON functionality with Entity Framework Core 6. It actually works exceedingly well with JSON_VALUE as shown below.

var results = _context.Pages.Where(p => MyDbFunctions.JsonValue(p._PublishedContent, "$.content").ToLower().Contains("test"));

DbContext is as follows:

public class JsonValueTestingContext : DbContext
{
    public JsonValueTestingContext(DbContextOptions context) : base(context) { }
    public DbSet<Page> Pages { get; set; }

    public DbSet<Template> Templates { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(() => MyDbFunctions.JsonValue(default(string), default(string)));
        modelBuilder.HasDbFunction(() => MyDbFunctions.JsonQuery(default(string), default(string)));
    }
}

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

    [DbFunction("JSON_QUERY", Schema = "", IsBuiltIn = true)]
    public static string JsonQuery(string source, [NotParameterized] string path) => throw new NotSupportedException();
}

The challenge I’m running into is that JSON_VALUE is good for basic types like string, int, boolean, datetime, etc. However, I do also store a string of arrays like [‘Apple’, ‘Orange’, ‘Pear’] and I would very much like to do something similar to the following:

var results = _context.Pages.Where(p => MyDbFunctions.JsonQuery(p._PublishedContent, "$.content").Contains("Apple"));

I can’t seem to figure out how to achieve the latter. If I try to return a string[] type for JSON_QUERY in MyDbFunctions, it says it is an invalid return type for the provider. I’ve tried all sorts of casting too, and Linq cannot translate. I feel like there must be a way.