Mixing EF Core Convensions and DBFunction for a JObject property

Question

I have an entity with properties of type JObject and I need to be able to use DbFunctions against those properties.

When I execute, the project throws an exception saying that DbFunction doesn't allows parameters of type JObject.

The entity is like...

public class OrchestrationRun
{
   public long Id { get; set; }
    public JObject MetaData { get; set; }
    public JObject SystemMetaData { get; set; }
}

The DbContext looks like...

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions options)
        : base(options)
    {
    }

    public virtual DbSet<OrchestrationRun> OrchestrationRun { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new OrchestrationRunConfiguration());

       // DbFunction mapping for JSON_VALUE
       modelBuilder.HasDbFunction( typeof(MyDbContext).GetMethod(nameof(JsonValue)))
                      .HasName("JSON_VALUE")
                      .HasSchema("");
    }

    // DbFunction
    public static string JsonValue(JObject column, [NotParameterized] string path) => throw new NotSupportedException();
}

The OrchestrationRunConfiguration is ...

public class OrchestrationRunConfiguration : IEntityTypeConfiguration<OrchestrationRun>
{
    public void Configure(EntityTypeBuilder<OrchestrationRun> builder)
    {
        builder.Property(e => e.MetaData).HasConversion(
            jObject => jObject != null ? jObject.ToString(Formatting.None) : null,
            json => string.IsNullOrWhiteSpace(json) ? null : JObject.Parse(json)
        );

        builder.Property(e => e.SystemMetaData).HasConversion(
             jObject => jObject != null ? jObject.ToString(Formatting.None): null,
             json => string.IsNullOrWhiteSpace(json) ? null : JObject.Parse(json)
         );
    }
}

The query I'm trying to execute is...

var dbResponse = (from or in this.dbContext.OrchestrationRun
where MyDbContext.JsonValue(or.MetaData,"$.Product.ProductCategoryName") == "EXAMPLE"
select new
       {
          Id = or.Id,
          CategoryId = "EXAMPLE"
       }

    ).ToList();

Note: The exception occurs at DbContext instantiation. Therefore the query is never called.

The exception thrown is...

System.InvalidOperationException: The parameter 'column' for the DbFunction 'MyDbContext.JsonValue' has an invalid type 'JObject'. Ensure the parameter type can be mapped by the current provider. at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateDbFunctions(IModel model) at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model) at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ValidatingConvention.Apply(InternalModelBuilder modelBuilder)

It looks like these two features, Database Functions and Value Conversions simply don't work together in this potentially useful way. I would create an issue at EF Core: https://github.com/aspnet/EntityFrameworkCore/issues — David Browne - Microsoft, May 22 '19 at 19:07
can you poste the SQL code of the "CREATE FUNCTION Json_Value..."? Which data type have you defined there? — tire0011, Oct 22 '19 at 15:14

Raghu · Answer 1 · 2021-11-21T23:33:43.873

The below code works for exactly same scenario..

            var jsonValueMethod = typeof(MyDbContext).GetMethod(nameof(MyDbContext.JsonValue));

            builder.HasDbFunction(jsonValueMethod)
                .HasTranslation(args => {
                    return SqlFunctionExpression.Create("JSON_VALUE", args, jsonValueMethod.ReturnType, null);
                })
                .HasParameter("column").Metadata.TypeMapping = new StringTypeMapping("NVARCHAR(MAX)");

The below line does the magic of converting the JObject column to NVARCHAR(MAX) or whatever your string data-type is.

UPDATE: Here is the syntax for EF Core 5 and above.

        protected override void OnModelCreating(ModelBuilder builder)
        {
            ....

            var jsonValueMethod = typeof(QueryExtentions).GetMethod(nameof(QueryExtentions.JsonValue));
            var stringTypeMapping = new StringTypeMapping("NVARCHAR(MAX)");

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

@Raghu SqlFunctionExpression.Create() works but it's marked as Obsolete in .NET 5. Can't figure out how to rewrite this to make it work. Any ideas? — Nexus, Dec 01 '20 at 12:37
@Nexus .HasTranslation(args => new SqlFunctionExpression("JSON_VALUE", args, nullable: true, argumentsPropagateNullability: new[] { false, false }, typeof(string), null)) — SeKoch, Jan 25 '21 at 10:26

score 2 · Answer 2 · answered Jan 25 '21 at 10:34

Thanks to Raghu, your answer helped me a lot. For users who may come here and want to mixin conversions with json functions and to update the answer of Raghu with the latest EF Core 5.0:

The jsonvalue function:

public static class JsonExtensions
{
    public static string JsonValue(object expression, string path) => throw new InvalidOperationException($"{nameof(JsonValue)} cannot be called client side");
}

In the DbContext OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
            var jsonValueMethodInfo = typeof(JsonExtensions).GetRuntimeMethod(nameof(JsonExtensions.JsonValue), new[] { typeof(string), typeof(string) });

            modelBuilder
                .HasDbFunction(jsonValueMethodInfo)
                .HasTranslation(args => new SqlFunctionExpression("JSON_VALUE", args, nullable: true, argumentsPropagateNullability: new[] { false, false }, typeof(string), null))
                .HasParameter("expression").Metadata.TypeMapping = new StringTypeMapping("NVARCHAR(MAX)"); // conversion

[...]

// example of conversion of a json property
                entity.Property(e => e.AdditionalProperties)
                    .HasColumnName("AdditionalJson")
                    .HasConversion(
                        v => Newtonsoft.Json.JsonConvert.SerializeObject(v, new Newtonsoft.Json.JsonSerializerSettings() { ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore }),
                        v => Newtonsoft.Json.JsonConvert.DeserializeObject<AdditionalUserProperties>(v)
                     );
}

Then you are able to use the extension methods with conversion properties... example:

    var testId = (from u in this.Users
                     join e in this.Employees.IgnoreQueryFilters() on JsonExtensions.JsonValue(u.AdditionalProperties, "$." + nameof(AdditionalUserProperties.PersonalId)) equals e.PersonalId
                     where u.Id == userId
                     select e.Id).FirstOrDefault();

The generated sql:

[...][e] ON JSON_VALUE([u].[AdditionalJson], N'$.PersonalId') = [e].[PersonalId][...]

Mixing EF Core Convensions and DBFunction for a JObject property

2 Answers2