How to persist columns as json using the table-per-hierarchy (TPH) pattern in Entity Framework Core 2

Question

I am trying to store a table using the table-per-hierarchy pattern but instead of columns for each derived field, I want to store it as json. I am simply doing the example from the inheritance section in the .net entity framework core documentation as such:

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RssBlog>().HasBaseType<Blog>();
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

public class RssBlog : Blog
{
    public string RssUrl { get; set; }
}

This will work fine and store every property as a column as seen in the screenshot here:

What i am trying to achieve is storing the properties as a json column instead of multiple columns. It would probably look something like this:

| BlogId | Discriminator | json                                                                                          |
|--------|---------------|-----------------------------------------------------------------------------------------------|
| 1      | Blog          | {"Url": "http://blogs.msdn.com/dotnet"}                                                       |
| 2      | RssBlog       | {"Url": "http://blogs.msdn.com/adonet", "RssUrl": "http://blogs.msdn.com/b/adonet/atom.aspx"} |

The main idea is that I want to store inherited objects of a type to the same table, exactly as the TPH pattern allows, but using a json column instead of multiple columns.

Is this possible to achieve in EF Core 2.2?

score 2 · Accepted Answer · answered May 02 '19 at 15:02

Shortly,

No.

This functionality is not native for EF and it's a question if ever will. But ... you can do it yourself.

You can build your own repository for this entities. Internally, it uses EF as a data source and you'll have to do all the mapping by yourself. You probably would have to build your own implementation of change tracking for proper update functionality.

Second approach could be packing whole entity in the JSON field and using EF as a simple data source. Then you can extract your entities with something like this...:


var data = DbContext.MyPolymorphData
    .Select(x => JsonConvert.Deserialize<IPolyEntity>(x.JsonField))
    .ToList();

.. using Json.NET package which allows storing object Type into the JSON along with the object data and deserialize it after respectively. But there will be still PITA with the update - after update you should serialize the data back to the mother entity and let EF decide if anything has changed. Ugh.

Anyway. Storing JSON packed entities into relational DB forces you to maintain compatibility with your entity model. When you change the entity properties, it will break, unless you didn't somehow managed to update all the JSONs baked in DB. If you have the option, avoid it.

How to persist columns as json using the table-per-hierarchy (TPH) pattern in Entity Framework Core 2

1 Answers1