How to store JSON in an entity field with EF Core?

Question

I am creating a reusable library using .NET Core (targeting .NETStandard 1.4) and I am using Entity Framework Core (and new to both). I have an entity class that looks like:

public class Campaign
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string Name { get; set; }

    public JObject ExtendedData { get; set; }
}

and I have a DbContext class that defines the DbSet:

public DbSet<Campaign> Campaigns { get; set; }

(I am also using the Repository pattern with DI, but I don't think that is relevant.)

My unit tests give me this error:

System.InvalidOperationException: Unable to determine the relationship represented by navigation property 'JToken.Parent' of type 'JContainer'. Either manually configure the relationship, or ignore this property from the model..

Is there a way to indicate that this is not a relationship but should be stored as a big string?

Answer 1

Going to answer this one differently.

Ideally the domain model should have no idea how data is stored. Adding backing fields and extra [NotMapped] properties is actually coupling your domain model to your infrastructure.

Remember - your domain is king, and not the database. The database is just being used to store parts of your domain.

Instead you can use EF Core's HasConversion() method on the EntityTypeBuilder object to convert between your type and JSON.

Given these 2 domain models:

public class Person
{
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(50)]
    public string LastName { get; set; }

    [Required]
    public DateTime DateOfBirth { get; set; }

    public IList<Address> Addresses { get; set; }      
}

public class Address
{
    public string Type { get; set; }
    public string Company { get; set; }
    public string Number { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
}

I have only added attributes that the domain is interested in - and not details that the DB would be interested in; I.E there is no [Key].

My DbContext has the following IEntityTypeConfiguration for the Person:

public class PersonsConfiguration : IEntityTypeConfiguration<Person>
{
    public void Configure(EntityTypeBuilder<Person> builder)
    {
        // This Converter will perform the conversion to and from Json to the desired type
        builder.Property(e => e.Addresses).HasConversion(
            v => JsonConvert.SerializeObject(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }),
            v => JsonConvert.DeserializeObject<IList<Address>>(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }));
    }
}

With this method you can completely decouple your domain from your infrastructure. No need for all the backing field & extra properties.

Answer 2

The key to making the the Change Tracker function correctly is to implement a ValueComparer as well as a ValueConverter. Below is an extension to implement such:

public static class ValueConversionExtensions
{
    public static PropertyBuilder<T> HasJsonConversion<T>(this PropertyBuilder<T> propertyBuilder) where T : class, new()
    {
        ValueConverter<T, string> converter = new ValueConverter<T, string>
        (
            v => JsonConvert.SerializeObject(v),
            v => JsonConvert.DeserializeObject<T>(v) ?? new T()
        );

        ValueComparer<T> comparer = new ValueComparer<T>
        (
            (l, r) => JsonConvert.SerializeObject(l) == JsonConvert.SerializeObject(r),
            v => v == null ? 0 : JsonConvert.SerializeObject(v).GetHashCode(),
            v => JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(v))
        );

        propertyBuilder.HasConversion(converter);
        propertyBuilder.Metadata.SetValueConverter(converter);
        propertyBuilder.Metadata.SetValueComparer(comparer);
        propertyBuilder.HasColumnType("jsonb");

        return propertyBuilder;
    }
}

Example of how this works.

public class Person
{
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(50)]
    public string LastName { get; set; }

    [Required]
    public DateTime DateOfBirth { get; set; }

    public List<Address> Addresses { get; set; }      
}

public class Address
{
    public string Type { get; set; }
    public string Company { get; set; }
    public string Number { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
}

public class PersonsConfiguration : IEntityTypeConfiguration<Person>
{
    public void Configure(EntityTypeBuilder<Person> builder)
    {
        // This Converter will perform the conversion to and from Json to the desired type
        builder.Property(e => e.Addresses).HasJsonConversion<IList<Address>>();
    }
}

This will make the ChangeTracker function correctly.

Answer 3

@Michael's answer got me on track but I implemented it a little differently. I ended up storing the value as a string in a private property and using it as a "Backing Field". The ExtendedData property then converted JObject to a string on set and vice versa on get:

public class Campaign
{
    // https://learn.microsoft.com/en-us/ef/core/modeling/backing-field
    private string _extendedData;

    [Key]
    public Guid Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string Name { get; set; }

    [NotMapped]
    public JObject ExtendedData
    {
        get
        {
            return JsonConvert.DeserializeObject<JObject>(string.IsNullOrEmpty(_extendedData) ? "{}" : _extendedData);
        }
        set
        {
            _extendedData = value.ToString();
        }
    }
}

To set _extendedData as a backing field, I added this to my context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Campaign>()
        .Property<string>("ExtendedDataStr")
        .HasField("_extendedData");
}

Update: Darren's answer to use EF Core Value Conversions (new to EF Core 2.1 - which didn't exist at the time of this answer) seems to be the best way to go at this point.

Answer 4

For those using EF 2.1 there is a nice little NuGet package EfCoreJsonValueConverter that makes it pretty simple.

using Innofactor.EfCoreJsonValueConverter;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class Campaign
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string Name { get; set; }

    public JObject ExtendedData { get; set; }
}

public class CampaignConfiguration : IEntityTypeConfiguration<Campaign> 
{
    public void Configure(EntityTypeBuilder<Campaign> builder) 
    {
        builder
            .Property(application => application.ExtendedData)
            .HasJsonValueConversion();
    }
}

Answer 5

I have made a solution based on Robert Raboud's contribution. The change made by me is that my implementation uses a HasJsonConversion method that depends on the System.Text.Json package rather than Newtonsofts library:

    public static PropertyBuilder<T> HasJsonConversion<T>(this PropertyBuilder<T> propertyBuilder) where T : class, new()
    {
        var options = new JsonSerializerOptions
        {
            PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
            WriteIndented = true,
            AllowTrailingCommas = true,
            PropertyNameCaseInsensitive = true
        };

        ValueConverter<T, string> converter = new ValueConverter<T, string>
        (
            v => JsonSerializer.Serialize(v, options),
            v => JsonSerializer.Deserialize<T>(v, options) ?? new T()
        );

        ValueComparer<T> comparer = new ValueComparer<T>
        (
            (l, r) => JsonSerializer.Serialize(l, options) == JsonSerializer.Serialize(r, options),
            v => v == null ? 0 : JsonSerializer.Serialize(v, options).GetHashCode(),
            v => JsonSerializer.Deserialize<T>(JsonSerializer.Serialize(v, options), options)
        );

        propertyBuilder.HasConversion(converter);
        propertyBuilder.Metadata.SetValueConverter(converter);
        propertyBuilder.Metadata.SetValueComparer(comparer);
        propertyBuilder.HasColumnType("LONGTEXT");

        return propertyBuilder;
    }

Note also that this implementation expects for the column to be LONGTEXT since I am using a MySQL setup.

Answer 6

Here's something I used

Model

public class FacilityModel 
{
    public string Name { get; set; } 
    public JObject Values { get; set; } 
}

Entity

[Table("facility", Schema = "public")]
public class Facility 
{
     public string Name { get; set; } 
     public Dictionary<string, string> Values { get; set; } = new Dictionary<string, string>();
}

Mapping

this.CreateMap<Facility, FacilityModel>().ReverseMap();

DBContext

base.OnModelCreating(builder); 
        builder.Entity<Facility>()
        .Property(b => b.Values)
        .HasColumnType("jsonb")
        .HasConversion(
        v => JsonConvert.SerializeObject(v),
        v => JsonConvert.DeserializeObject<Dictionary<string, string>>(v));

Answer 7

Could you try something like this?

    [NotMapped]
    private JObject extraData;

    [NotMapped]
    public JObject ExtraData
    {
        get { return extraData; }
        set { extraData = value; }
    }

    [Column("ExtraData")]
    public string ExtraDataStr
    {
        get
        {
            return this.extraData.ToString();
        }
        set
        {
            this.extraData = JsonConvert.DeserializeObject<JObject>(value);
        }
    }

here is the migration output:

ExtraData = table.Column<string>(nullable: true),

Answer 8

For developers, who work with EF Core 3.1 and meet such error ("The entity type 'XXX' requires a primary key to be defined. If you intended to use a keyless entity type call 'HasNoKey()'.") the solution is:

Move .HasConversion() method with it's lambda from: OrderConfiguration : IEntityTypeConfiguration<T> to OnModelCreating(ModelBuilder modelBuilder) in your DataContext.

Answer 9

----------------- 2022 Update -----------------

Hey there,

Just sharing an update from Dec/2022.

Recently, EF Core 7.0 was launched, containing one of the most awaited features called JSON Columns.

This new feature allows us for mapping aggregates (written from .NET types) into JSON documents.

Just remember that, In EF Core, aggregate types are defined using Owned Entity Types. https://learn.microsoft.com/en-us/ef/core/modeling/owned-entities

Let's consider this scenario: A object called "LogDetail", that needs to be stored into a single column as a JSON, inside another object (or table) called "Log".

public class Log : BaseEntity
{
    public string TraceID { get; set; } = string.Empty;
    public string Code { get; set; } = string.Empty;
    public LogDetail LogDetail { get; set; } = null!;
    public string IpAddress { get; set; } = string.Empty;
}


public class LogDetail
{
    public string InnerException { get; set; } = null!;
    public string MemberMap { get; set; } = null!;
    public string Message { get; set; } = null!;
    public string Source { get; set; } = null!;
    public string StackTrace { get; set; } = null!;
    public string TypeMap { get; set; } = null!;
    public string Path { get; set; } = null!;
}

By the end, all that you need is to configure the behavior on your configuration map class:

public sealed class LogMap : IEntityTypeConfiguration<Log>
{
    public override void Configure(EntityTypeBuilder<Log> builder)
    {
        /* Owned Type Configurations */
        builder.OwnsOne(e => e.LogDetail, options =>
        {
            options.ToJson("LOG_DETAIL");
        });

    }
}

Answer 10

For those who are working on entity framework core 5.0 and above. below can work if you are getting error like below

The entity type '{EntityName}' requires a primary key to be defined. If you intended to use a keyless entity type, call 'HasNoKey' in 'OnModelCreating'. The error is asking to define a primary key on the model

Try this

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Person>(
                eb =>
                {
                    eb.Property(p => p.Addresses).HasConversion(

        v => JsonConvert.SerializeObject(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }),
        v => JsonConvert.DeserializeObject<IList<Address>>(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })
                        );
                });
    }

Answer 11

// DbContext

  protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            var entityTypes = modelBuilder.Model.GetEntityTypes();
            foreach (var entityType in entityTypes)
            {
                foreach (var property in entityType.ClrType.GetProperties().Where(x => x != null && x.GetCustomAttribute<HasJsonConversionAttribute>() != null))
                {
                    modelBuilder.Entity(entityType.ClrType)
                        .Property(property.PropertyType, property.Name)
                        .HasJsonConversion();
                }
            }

            base.OnModelCreating(modelBuilder);
        }

Create an attribute to handle the properties of the entities.

public class HasJsonConversionAttribute : System.Attribute
    {

    }

Create extention class to find Josn properties

    public static class ValueConversionExtensions
    {
        public static PropertyBuilder HasJsonConversion(this PropertyBuilder propertyBuilder)
        {
            ParameterExpression parameter1 = Expression.Parameter(propertyBuilder.Metadata.ClrType, "v");

            MethodInfo methodInfo1 = typeof(Newtonsoft.Json.JsonConvert).GetMethod("SerializeObject", types: new Type[] { typeof(object) });
            MethodCallExpression expression1 = Expression.Call(methodInfo1 ?? throw new Exception("Method not found"), parameter1);

            ParameterExpression parameter2 = Expression.Parameter(typeof(string), "v");
            MethodInfo methodInfo2 = typeof(Newtonsoft.Json.JsonConvert).GetMethod("DeserializeObject", 1, BindingFlags.Static | BindingFlags.Public, Type.DefaultBinder, CallingConventions.Any, types: new Type[] { typeof(string) }, null)?.MakeGenericMethod(propertyBuilder.Metadata.ClrType) ?? throw new Exception("Method not found");
            MethodCallExpression expression2 = Expression.Call(methodInfo2, parameter2);

            var converter = Activator.CreateInstance(typeof(ValueConverter<,>).MakeGenericType(typeof(List<AttributeValue>), typeof(string)), new object[]
                {
                    Expression.Lambda( expression1,parameter1),
                    Expression.Lambda( expression2,parameter2),
                    (ConverterMappingHints) null
                });

            propertyBuilder.HasConversion(converter as ValueConverter);

            return propertyBuilder;
        }
    }

Entity example

 public class Attribute
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }

        [HasJsonConversion]
        public List<AttributeValue> Values { get; set; }
    }

    public class AttributeValue
    {
        public string Value { get; set; }
        public IList<AttributeValueTranslation> Translations { get; set; }
    }

    public class AttributeValueTranslation
    {
        public string Translation { get; set; }

        public string CultureName { get; set; }
    }

Download Source

Answer 12

The comment by @Métoule:

Be careful with this approach: EF Core marks an entity as modified only if the field is assigned to. So if you use person.Addresses.Add, the entity won't be flagged as updated; you'll need to call the property setter person.Addresses = updatedAddresses.

made me take a different approach so that this fact is obvious: use Getter and Setter methods, rather than a property.

public void SetExtendedData(JObject extendedData) {
    ExtendedData = JsonConvert.SerializeObject(extendedData);
    _deserializedExtendedData = extendedData;
}

//just to prevent deserializing more than once unnecessarily
private JObject _deserializedExtendedData;

public JObject GetExtendedData() {
    if (_extendedData != null) return _deserializedExtendedData;
    _deserializedExtendedData = string.IsNullOrEmpty(ExtendedData) ? null : JsonConvert.DeserializeObject<JObject>(ExtendedData);
    return _deserializedExtendedData;
}

You could theoretically do this:

campaign.GetExtendedData().Add(something);

But it's much more clear that That Doesn't Do What You Think It Does™.

If you're using database-first and using some kind of class auto-generator for EF, then the classes will usually be declared as partial, so you can add this stuff in a separate file that won't get blown away the next time you update your classes from your database.

Answer 13

Use JsonProperty.EFCore. You can install it as a nuget package.

Example:

using JsonProperty.EFCore;

//Create product
var product = new Product() { Name = "Car" };

//AddRange
product.Attributes.AddRange(new Dictionary<string, object>() {
    //You can add values ​​of different types if the base type is object 
    {"MaxSpeed",300},{ "Engine capacity",3.5}, { "ElectroCar",false }
});

//Add
product.Attributes.Add("Color", "Red");

//Edit
product.Attributes.Edit(attrs=>{
    attrs["Color"] = "Blue";
    return attrs;
});

//Entity model
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    //Json container property. Same type as JsonDictionary<string,object>
    public JsonDictionary Attributes{ get; set; } = new();
}