Using JSON_QUERY as DbFunction in EF Core 6

Question

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.

JSON_QUERY returns a nvarchar(max) so doesn't really make sense to do Contains anyway. Perhaps you want the table valued function OPENJSON instead? — Charlieface
– Charlieface, Commented Jun 26, 2022 at 1:16
Thanks, with your suggestion I was able to solve it. I'll post the full solution as an answer for future visitors to this post. — apexdodge
– apexdodge, Commented Jun 26, 2022 at 1:34
Note by the way that OPENJSON has two versions: without a WITH schema or including one. Without one means you can break open arrays containing bare values such as strings or numbers into [key], value, type where [key] is the index. If you supply a schema then you can break open arrays containing objects, but you don't get the index. Supplying a WITH schema is going to be difficult in EF, and the fact the returned table is dynamic makes things worse also. — Charlieface
– Charlieface, Commented Jun 26, 2022 at 1:38
Yes, I went with the one without the with, just key and value only since it's just a raw string of arrays. I posted my answer now. Thanks for the help. — apexdodge
– apexdodge, Commented Jun 26, 2022 at 1:43

apexdodge · Accepted Answer · 2022-06-26 01:39:30Z

4

The answer is to use OPENJSON, not JSON_QUERY in this scenario. On top of that there are some additional considerations. For example, you need to create a Keyless object with Key / Value attributes and specify IQueryable as the return type on the OPENJSON static method. See example below.

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.OpenJson(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("OPENJSON", Schema = "", IsBuiltIn = true)]
    public static IQueryable<ArrayDatabaseItem> OpenJson(string source, [NotParameterized] string path) => throw new NotSupportedException();
}

[Keyless]
public class ArrayDatabaseItem
{
    public int Key { get; set;  }
    public string Value { get; set;  }
}

And usage may look like:

var results = _context.Pages.Where(p => MyDbFunctions.OpenJson(p._PublishedContent, "$.array_item").Any(c => c.Value == "Choice 2"));

@Charlieface's comment and this part of Microsoft Docs helped me arrive at the answer.

answered Jun 26, 2022 at 1:39

apexdodge

7,0574 gold badges29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlieface Over a year ago

You could add another property int Type. Also the path is optional (default is $)

Svyatoslav Danyliv Over a year ago

You can also put link to EF Core documentation

Collectives™ on Stack Overflow

Using JSON_QUERY as DbFunction in EF Core 6

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related