How to Store JSON in an Entity Field with Ef Core

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

@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.

Best way to update JSON property with EF core

Its not possible as of EF Core 2.2. There is a proposal for such a feature, but I would not hold my breath.

If the JSON you are storing is small and/or the updates are rare I would go with the first approach as the changes are stored in the context and you don't need a transaction and aligns better with the overall design of EF. Plus it gives you compile time safety and is easier to refactor/change. Check this thread out - How to store JSON in an entity field with EF Core?

If you need performance than definitely the JSON_MODIFY sql command.

Searching in sql server json column and consume it using entity framework core

Well - you could use Value conversion:

https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#composite-value-objects

Another option would be to create a keyless entity for that specific query:

https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations

Edit: here's a bit on how to setup a keyless entity.
https://stackoverflow.com/a/69924584/4122889

Mapping json in EF Core 3

Since you're saving the City.Stores collection as a JSON serialized string, you cannot use it as a navigation collection to child objects. You need to get rid of the .Include(x => x.Stores) in the query statement:

var res = _db.Cities.Where(x => x.Id == id)
                .Include(x => x.Parks)  // this works fine           
                .FirstOrDefault();

Save Collection As JSON with Entity Framework

The problem with the accepted answer is that any changes to the contents of the list (adding, modifying or deleting entries) won't be tracked by EF.

Here is my solution, inspired by this excellent blog post.

This class takes care of serializing and deserializing so that the collection can be stored in a single column on the parent model:

[ComplexType]
public class DateTimeCollection : Collection<DateTime>
{
    public void AddRange(IEnumerable<DateTime> collection)
    {
        foreach (var item in collection)
        {
            Add(item);
        }
    }

    [Column("Times")]
    public string Serialized
    {
        get { return JsonConvert.SerializeObject(this); }
        private set
        {
            if (string.IsNullOrEmpty(value))
            {
                Clear();
                return;
            }

            var items = JsonConvert.DeserializeObject<DateTime[]>(value);
            Clear();
            AddRange(items);
        }
    }
}

That's it! You can now use this new collection on your parent class exactly as you'd expect. Changes to the contents of the collection will be tracked.

public class Company{
    // ...
    public DateTimeCollection Times { get; set; }
}

Entity Framework Core jsonb column type

Based on H. Herzl comment:

My final solution was something like this:

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

    [Column(TypeName = "jsonb")]
    public string Data { get; set; }
}

Migrations generated this:

Data = table.Column<string>(type: "jsonb", nullable: true),

When updated the database with migrations, the Data column was created correctly with jsonb type.

Thank you H. Herzl!

---

Related Topics