Map JSON values stored in database to EF properties

[JocaPC]

SQL Server, PostgreSQL, MySQL, and Oracle databases enable developers to store JSON in columns and use values from JSON documents in queries. As an example, we can create product table with few fixed columns and variable information stored as JSON:

Product[id,title,decription,datecreated,info]

info column can contain JSON text. Use cases are:

1. I can put different non-standard information about products in JSON column as a set of key:value pairs in JSON column, e.g. {"color":"red","price":35.99,"status":1}.
2. I can have new kind of single table inheritance where I can put all properties specific to some leaf classes as a bag of key:values instead of additional columns.

In SQL Server/Oracle we can use JSON_VALUE function to read JSON values from JSN column by keys, e.g.:

SELECT id, title,
       json_value(info,'$.price'),json_value(info,'$.color'),json_value(info,'$.status')
FROM product

PostgreSQL has ->> operator and MySQL has json_extract function that are similar.

Problem

Currently, JSON fields can be mapped to entities in entity framework as string properties and we can parse them using Json.Net library. It would be good if we could map properties in EF model to (JSON text+path).

Proposal

1. Basic - Could we map property from EF model to string column that contains text and specify path of value in JSON (e.g. price, status) ? EF can extract json value on JSON path and populate property in EF model with proper type casting.
2. Medium - Enable updates of properties mapped to json value. If some EF property that is mapped to JSON values is updated, EF could format all of them as JSON and save them back in the in JSON column.
3. Advanced - Enable LINQ support over JSON properties. If we use Select(obj=>obj.price), or Where(obj => obj.price < 100) in LINQ queries, these predicates could be transformed to JSON_VALUE for SQL Server/Oracle, -> for PostgreSQL. This way we will be able to query JSON values directly from EF.

edited by @smitpatel on 16th March 2018

You can use JSON_VALUE function using user defined function feature in EF Core 2.0
#11295 (comment) explains how to do it for SqlServer.

Some open questions/possible subtasks:

• Partial updates to JSON documents (would likely require modeling as owned entities)
• Allow configuring JsonSerializationOptions to customize JSON property name etc., on a property-by-property basis (but also allow some bulk configuration default?)
• Use or allow using the new System.Text.Json source generator introduced in .NET 6.0?
• Indexes should probably out of scope of general relational support, as support differs considerably across databases.

Issues

• Support inheritence with JSON POCO mapping #27779

[divega]

@JocaPC We are of course busy completing more basic functionality than this at the moment but eventually I would love us to enable all of this. BTW, I think providing a way to declare indexes for values in the JSON payload (so that those queries can be optmized) would also be nice.

[gdoron]

@rowanmiller
It would be helpful and user friendly if we could add to our POCO a dynamic property and through the modelbuilder mark it as JSON (modelbuilder.Entity<Product>().Property(x=> x.CustomFields).AsJson() where CustomFields is dynamic/ ExpandoObject).

And then we will be able to nicely query the dynamic object like:

context.Products.Where(x=> x.InsertDate > DateTime.Now && x.CustomFields.Retailer.Name == "Doron")

// Or 
context.Products.Where(x=> x.InsertDate > DateTime.Now && 
                           x.CustomFields.Retailer.Items.Contains("Foo"))

Though I'm just not sure how indexing will work in SQL-Server 2016 for JSON columns @JocaPC

OData is using something similar with Open Types and it's intuitive and very easy to use.

[omric-axonize]

OData Open Type story sounds like a perfect fit to SQL-Server 2016 + EF Core!

[JocaPC]

You can add index on computed column that exposes JSON value:

1. Create non-persisted computed column with expression JSON_VALUE(jsonCol, '$.Retailer.Name')
2. Create index on that computed column

Original queries don't need to be rewritten. When SQL Server finds a query that uses JSON_VALUE and if path in JSON_VALUE matches computed column that has index, it will use indexing. See
https://msdn.microsoft.com/en-us/library/mt612798.aspx

[erichexter]

I would like to see this support.

[DaveSlinn]

My team is currently investigating EF for a new application we are undertaking and I was hoping to use this very feature. If we were so inclined to invest in working on this feature, for our own selfish needs, would that be worthwhile contributing?

[ikourfaln]

We realy need this functionality

[ikourfaln]

At the moment, I think that we can just use FromSql function to execute raw SQL and include JSON_VALUE.
example:

var contacts = _context.Contacts.FromSql("SELECT Id, Name, Address, City, State, Zip " +
                                        "FROM Contacts " +
                                        "WHERE JSON_VALUE(Info, '$.Moniker') = @p1", moniker1);

[soycabanillas]

My two cents (what I'm doing for now) for proposal 1:

I configure my entities this way:

public class ConfigCTransfer : EntityMappingConfiguration<CTransfer>
{
    public override void Map(EntityTypeBuilder<CTransfer> entity)
    {
        entity.HasJsonValue(x => x.Serialized, y => y.Data.Status, z => z.FStatus);
    }
}

x.Serialized is the JSON/text field.
y.Data is the object that is serialized into Serialized. Status is just a property of Data.
z.FStatus is the column where I'm going to store the value of y.Data.Status.

the heavyweight is done by the HasJson extension method. I include the code (not a clean one) for copy paste, but the important thing here is that I'm setting the FStatus property as a computed column, as @JocaPC commented.

    public static void HasJsonValue<T, U>(this EntityTypeBuilder<T> value, Expression<Func<T, string>> jsonFieldExpr, Expression<Func<T, U>> jsonPathExpr, Expression<Func<T, U>> dataFieldExpr) where T : class
    {
        var jsonPathSegments = PathFromExpression(jsonPathExpr);
        jsonPathSegments.RemoveAt(0);
        var jsonPath = string.Join(".", jsonPathSegments);

        var jsonFieldSegments = PathFromExpression(jsonFieldExpr);
        var jsonField = string.Join(".", jsonFieldSegments);

        var sqlDataType = new SqlServerTypeMapper().FindMapping(typeof(U));
        //var typeName =  sqlDataType.DefaultTypeName;
        var typeName = sqlDataType.StoreType;
        var property = value.Property(dataFieldExpr);
        var isNullable = property.Metadata.IsNullable;
        var isForeignKey = property.Metadata.IsForeignKey();
        var maxLength = property.Metadata.GetMaxLength();
        if (maxLength != null) typeName = typeName.Replace("(max)", $"({maxLength.Value})");
        var columnDefinition = $"CAST((json_value([{jsonField}],'$.{jsonPath}')) AS {typeName})";
        if (isNullable == false || isForeignKey) columnDefinition = columnDefinition + " PERSISTED";
        if (isNullable == false) columnDefinition = columnDefinition + " NOT NULL";
        value.Property(dataFieldExpr).HasComputedColumnSql(columnDefinition);
    }

    //For other solutions, see:
    //http://stackoverflow.com/questions/1667408/c-getting-names-of-properties-in-a-chain-from-lambda-expression
    //and
    //http://stackoverflow.com/questions/2789504/get-the-property-as-a-string-from-an-expressionfunctmodel-tproperty
    public static List<string> PathFromExpression<T, P>(Expression<Func<T, P>> expr)
    {
        var result = new List<string>();
        MemberExpression me;
        switch (expr.Body.NodeType)
        {
            case ExpressionType.Convert:
            case ExpressionType.ConvertChecked:
                var ue = expr.Body as UnaryExpression;
                me = ((ue != null) ? ue.Operand : null) as MemberExpression;
                break;
            default:
                me = expr.Body as MemberExpression;
                break;
        }

        while (me != null)
        {
            string propertyName = me.Member.Name;
            //Type propertyType = me.Type;
            result.Add(propertyName);
            //Console.WriteLine(propertyName + ": " + propertyType);

            me = me.Expression as MemberExpression;
        }
        result.Reverse();
        return result;
    }

The EntityMappingConfiguration thing is just a friendly way of having the configuration of an entity in its own class. You can see the implementation and the discussion here:
https://github.com/aspnet/EntityFramework/issues/2805#issuecomment-218548872

If anyone needs helps with this, just ask me.

This has several limitations, though, at least:
1 - The properties are computed columns. The value will not be set until the entity is saved. But it will be of use for indexing.
2 - It doesn't support polymorphism, at least not in an easy/clear way. You can't use the same data for different entities... or I haven't found the way:

a - You can't use different classes to point to the very same table #6001
b - You can't use different classes to point to the very same field #240

Sorry if something about my conclusions is not completely accurate. There are others more capable of validating them. I just tried to expose my experience so far.

[soycabanillas]

And...

3 - This solution only supports SQL Server. No support for in memory database.

[bjorn-ali-goransson]

I think the comment of @ikourfaln is superiour (and simplest to implement, ie already done) in combination with the flexible mapping idea.

[ma3yta]

When it will be done?

[ikourfaln]

@ma3yta 😞
not yet decided. must be moved from Backlog to a Milestone

[bjorn-ali-goransson]

I find the implementation complexity for this feature, compared with its
usefulness, to be amazingly disproportionate.

2016-09-29 16:48 GMT+02:00 IKOURFALN Slimane notifications@github.com:

@ma3yta https://github.com/Ma3yTa 😞
not yet decided. must be moved from Backlog to a Milestone

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#4021 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAoyABSu8XPPrfjW7sIuoEmL_Vfjs_zsks5qu8_GgaJpZM4Gxzpa
.

[adrien-constant]

I also think this would be very interesting to have JSON query support in EF Core.

[bjorn-ali-goransson]

Actually, for me, LINQ support would be secondary (or ternary) to the actual mapping functionality. Nice, of course, but if it takes much more time the "milestones" should be split up.

IMO ... LINQ (as in: the query expression parsing bit) is a leaky abstraction that's not all that useful. It's often quite confusing. The IEnumerable extensions are stellar, though, the all-time best idea that the .NET team has conceived of. But I digress.

[bjorn-ali-goransson]

Is it possible to work around this limitation in todays codebase?

For example, I'd like a simple integer to be mapped to a Link object that contains an ID ...

[rowanmiller]

@bjorn-ali-goransson it's not natively supported but with Field Mapping support (added in 1.1 - currently in preview) you could map the integer to a private field and then have a property that converts to/from the Link object. See this post for details on 1.1 Preview1 and how to use field mapping https://blogs.msdn.microsoft.com/dotnet/2016/10/25/announcing-entity-framework-core-1-1-preview-1/.

[rpokrovskij]

Why just not to start from simpler things: smartly serialize entity (with all its "navigation") to JSON using model information to avoid circular references? Together you will be able just to insert JSON DB fields "as is". Then user will be able easy to send the serialized result to further layers (usually SPA). It should cover 95% of JSON DB field usages.

[DenisAgarev]

Yes, agree that we want mapping for just selection. It's not critical to serialize/deserialize on update but would be very helpful with odata sorting and filtering.

[ma3yta]

Good to have something like UserType in NHibernate http://blog.denouter.net/2015/03/json-serialized-object-in-nhibernate.html?m=1

[ma3yta]

@rowanmiller maybe this helps to implement json support https://www.linkedin.com/pulse/quering-json-using-hibernate-marvin-froeder

http://jasperfx.github.io/marten/

https://github.com/jasperfx/marten

[jovanpop-msft]

@rowanmiller @divega Maybe JSON mapping could be implemented using backing fields. Here is a proposal: #7394

[marchy]

How is this feature tracking for the EF7 release?
Any expectation on when we can start getting our hands on it?

[aaronhudon]

How is this feature tracking for the EF7 release?
Any expectation on when we can start getting our hands on it?

7 or 8 more years. Just be patient.

[marchy]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long!

Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

[aaronhudon]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long!

Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

[marchy]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long!
Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Well... we DID have a migration to Postgres scheduled for this year so we can take advantage of the Postgres provider's implementation of this, but now with the announcement of its standardization we figured we'll wait for it instead.

Of course, that is, unless it doesn't drop this year.

Which brings me back to my question....

[mguinness]

Of course, that is, unless it doesn't drop this year.

I doubt you will get any type of commitment at this stage. Keep an eye on Plan for Entity Framework Core 7.0 and 7.0.0 milestone and hope it stays in - #13947 was recently punted.

[stevendarby]

How is this feature tracking for the EF7 release?

Any expectation on when we can start getting our hands on it?

There's a draft PR with initial work #28171

[mguinness]

That's promising, but a draft PR #24909 with initial work for value conversions was subsquently punted. Hopefully JSON columns makes it into preview 7 or 8, otherwise it's cutting it close for the November release.

[aaronhudon]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long!
Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Well... we DID have a migration to Postgres scheduled for this year so we can take advantage of the Postgres provider's implementation of this, but now with the announcement of its standardization we figured we'll wait for it instead.

Of course, that is, unless it doesn't drop this year.

Which brings me back to my question....

Switch to Postgres (if you can) and don’t look back.

[MeepTech]

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Could you possibly explain how or know any links about this? I've been looking myself.

[aaronhudon]

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Could you possibly explain how or know any links about this? I've been looking myself.

I have had both of the following approaches in production for years.

EF v6 ("OG")
This article will get you most of the way.
https://stackoverflow.com/a/50490674/459102

EF Core v2.2
It's a bit easier.

Add these to your DbContext (assumes the class name of your DbContext is SqlContext):

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

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

Then in OnModelCreating map the functions

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(typeof(SqlContext).GetMethod(nameof(JsonValue)))
                .HasName("JSON_VALUE")
                .HasSchema("");

            modelBuilder.HasDbFunction(typeof(SqlContext).GetMethod(nameof(JsonQuery)))
                .HasName("JSON_QUERY")
                .HasSchema("");

[maumar]

initial support is now checked in: 4018482 and 0577a3a, closing this issue

[MoazAlkharfan]

Will there be support for JsonSerializationOptions?

[maumar]

@MoazAlkharfan eventually, we have #28815 to track this