How to Store JSON in an Entity Field With EF Core

In this article, we are going to talk about how to store data in the JSON entity field using Entity Framework Core.

But before we start, we want to take a brief look at examples where we might decide to store data in JSON columns.

Let’s start

Why Should We Store JSON in an Entity Field

Sometimes we as software developers want to make a design decision about the structure of data in our application. An example is when application requirements are still evolving and the database schema is likely to change frequently. In such situations, we may come to a decision to handle this frequent change by storing some objects in  JSON columns in our database. We may later choose to convert those JSON columns to multiple separate tables after the change frequency becomes steady.

Moreover, we may decide to store some objects in JSON fields because the nature of the application dictates a dynamic structure. An example is when we store REST API responses in JSON format in our database. In this case, since the response structure is different for every endpoint that we have designed for a different purpose, we prefer to store it in a JSON column. 

Another example is when we want to access those objects only through the owning object and other entities do not have any relationship with them and we do not need to perform frequent queries and database joins against those objects in our application. For example, imagine we have an application that tracks customer purchases, and we want to store additional information about each purchase that’s not required for reporting or searching, and we only need to access it when viewing a particular purchase. In this case, we could store the additional purchase information in a JSON column in our database table.

Now let’s see implementing JSON columns in action using Entity Framework Core.

Create the Entity Model

Let’s assume we have an Order Management application. So, we have an entity called Order:

public class Order
{
    [Key]
    public int Id { get; set; }

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

    [Required]
    public string CustomerName { get; set; }

    [Required]
    public ShippingInfo ShippingInfo { set; get; }
}

And ShippingInfo, Shipment, and Delivery classes:

public class ShippingInfo
{
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode{ get; set; }

    public List<Shipment> Shipments { get; } = new();
    public List<Delivery> Deliveries { get; } = new();
}

public class Shipment
{
    public string TrackingNumber { get; set; }
    public DateTime ShipDate { get; set; }
    public string Carrier { get; set; }
}

public class Delivery
{
    public string ReceiverName { get; set; }
    public DateTime DeliveryDate { get; set; }
    public string Signature { get; set; }
}

We want to Store ShippingInfo with all its properties including navigation properties such as Shipments and Deliveries in a JSON column instead of different tables in the database. So we might choose to define that property with a JSON data type (like JObject) however, for separation of concerns it’s better to decouple the domain model from the infrastructure (which is the database here). As a result, we are going to implement it in such a way that we pass on the format of storing to the infrastructure layer. We are going to accomplish this by configuring the mapping of our domain model data type to JSON using Entity Framework Core.

JSON Column Mapping

We are going to use Fluent API to configure the relationship between Order and ShippingInfo. Microsoft introduced JSON Columns in Entity Framework Core 7. So by using JSON columns, we can have some navigation properties stored in JSON columns in our database and not have separate tables for them. This way, when we write a LINQ query for the JSON columns, the query translates to SQL query with ease and no error.

To use this new feature we use owned entities. In this type of relationship, we have an owner, which would be Order, and a dependent entity, which would be ShippingInfo. The dependent entity cannot exist without its owner. This relationship is similar to aggregates.

Let’s write the relationship between Order and ShippingInfo using the OwnsOne method in our OrderContext class inside the OnModelCreating  method:

public class OrderContext : DbContext
{
    public OrderContext(DbContextOptions<OrderContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().OwnsOne(
        order => order.ShippingInfo, ownedNavigationBuilder =>
        {
            ownedNavigationBuilder.ToJson();
            ownedNavigationBuilder.OwnsMany(si => si.Shipments);
            ownedNavigationBuilder.OwnsMany(si => si.Deliveries);
        });
    }

    public DbSet<Order> Orders { get; set; }
}

Here we define both relationships between ShippingInfo and Shipments and also ShippingInfo and Deliveries in the form of an owned entity. This time we define those relationships with the OwnsMany method since the dependent side of the relationship is a list. We also use ToJson() method to indicate that the navigation property will be kept in a JSON column. 

Create a Record Containing a JSON Column 

We would like to create an Order record with all its properties including its navigation properties in our database. So we are going to create a controller class called OrderController.

Let’s write the AddOrder action for our OrderController class:

[HttpPost]
public async Task<IActionResult> AddOrder(Order order)
{
    order.Id = Guid.NewGuid();
    order.OrderDate = DateTime.Now;
    _context.Orders.Add(order);
    await _context.SaveChangesAsync();

    return CreatedAtRoute(new {action: nameof(OrderController.GetOrderById), id: order.Id }, order);
}

After calling the API and getting a 201 status code, we can inspect the Orders table to check the result:

Now we would like to add Delivery objects to the Delivery list of the Order object. To accomplish this, let’s add the AddDeliveryToOrder HTTP POST action to our controller class:

[HttpPost("{id}/deliveries")]
public async Task<IActionResult> AddDeliveryToOrder(Guid id, Delivery delivery)
{
    var order = _context.Orders.Where(p => p.Id == id).FirstOrDefault();
    if (order is null)
        return NotFound();

    order.ShippingInfo.Deliveries.Add(delivery);
    await _context.SaveChangesAsync();

    return CreatedAtRoute(new { action = nameof(OrderController.GetOrderById), id = order.Id }, order);
}

In this action, we take the id of the Order as input in the URL and take the new Delivery object in the body section of the request. The URL for this action in Postman would be:

http://localhost:5179/api/order/3e5d1ffa-0502-43ce-8502-c1d4ee9060ed/deliveries

And the JSON body part:

{
  "receiverName":"John Smith",
  "deliveryDate":"2023-04-08",
  "signature":"John Smith..._"
}

Now when we inspect the JSON column ShippingInfo in the table, we are going to find that a new Delivery entry is added to it:

{"Address":"No 20,E 7th Street","City":"New York","PostalCode":"4580053","State":"New York","Deliveries":
[{"DeliveryDate":"2023-04-08T00:00:00","ReceiverName":"John Smith","Signature":"John Smith..._"}],
"Shipments":[{"Carrier":"Track LLC","ShipDate":"2023-04-05T00:00:00","TrackingNumber":"A120145"}]}

Read Records Containing JSON Column

We want to query orders using a JSON property such as City. Let’s add an HTTP GET action to our controller class:

[HttpGet("city/{city}")]
public async Task<IActionResult> GetOrdersByCity(string city)
{
    var orders = await _context.Orders.Where(o=>o.ShippingInfo.City == city).ToListAsync();

    return Ok(orders);
}

When we call the API using Postman, we will find the JSON result:

[
    {
        "id": "bb03acdb-4a55-478c-a0d1-2252b11f793a",
        "orderDate": "2023-04-08T17:30:32.0603699",
        "customerName": "Ben Stiller",
        "shippingInfo": {
            "address":   "No 6,E 8th Street",
            "city": "New York",
            "state": "New York",
            "postalCode": "4581163",
            "shipments": [
                {
                    "trackingNumber": "A120150",
                    "shipDate": "2023-04-05T00:00:00",
                    "carrier": "Swan LLC"
                }
            ],
            "deliveries": []
        }
    }
]

Excellent, Now we can move on.

Update a Record Containing a JSON Column

We would like to write a method that can update specific properties of the JSON navigation property Order entity; therefore, we can do the job by performing a partial update. Let’s write the HTTP PATCH action to perform the partial update:

[HttpPatch("{id}")]
public async Task<IActionResult> UpdateOrder(Guid id, JsonPatchDocument<Order> patch)
{
    var findOrder = _context.Orders.Where(p => p.Id == id).FirstOrDefault();
    if (findOrder is null)
        return NotFound();

    patch.ApplyTo(findOrder);
    await _context.SaveChangesAsync();

    return NoContent();
}

We have to pay attention that the UpdateOrder action method is exposed to any sort of PATCH operation (replace, test, copy, move, remove, and add); but we want to restrict any operations other than replace. To carry out the job, we need to write a Custom Action Filter Attribute and put the attribute at the top of the UpdateOrder method. For brevity, we are not going to cover this part in this article.

Now let’s call the HTTP PATCH method using Postman. The URL would be:

http://localhost:5179/api/order/3e5d1ffa-0502-43ce-8502-c1d4ee9060ed

And in the body section of the request, we write:

[
    {
        "op":"replace",
        "path":"/ShippingInfo/Address",
        "value":"Park Ave"
    }
]

After we call the endpoint, we get the 204 Status code. Now once we inspect ShippingInfo JSON column in the table, we are going to find that Address property in it has changed accordingly:

{"Address":"Park Ave","City":"New York","PostalCode":"4580053","State":"New York","Deliveries":
[{"DeliveryDate":"2023-04-08T00:00:00","ReceiverName":"John Smith","Signature":"John Smith..._"}],
"Shipments":[{"Carrier":"Track LLC","ShipDate":"2023-04-05T00:00:00","TrackingNumber":"A120145"}]}

Delete a Record Containing a JSON Column

We would like to delete the Delivery object that we previously added to the Delivery list of a specific Order record. To do that, let’s write the DeleteDeliveryFromOrder HTTP DELETE action in our controller class:

[HttpDelete("{id}/deliveries")]
public async Task<IActionResult> DeleteDeliveryFromOrder(Guid id, Delivery delivery)
{
    var findOrder = _context.Orders.Where(p => p.Id == id).FirstOrDefault();
    if (findOrder is null)
        return NotFound();

    if (!findOrder.ShippingInfo.Deliveries.Any())
        return NotFound();

    var findDelivery = findOrder.ShippingInfo.Deliveries
                .FirstOrDefault(d => d.ReceiverName == delivery.ReceiverName
                && d.DeliveryDate == delivery.DeliveryDate
                && d.Signature == delivery.Signature);
    if (findDelivery is null)
        return NotFound();

    findOrder.ShippingInfo.Deliveries.Remove(findDelivery);
    await _context.SaveChangesAsync();

    return NoContent();
}

After we call the endpoint using Postman, we can verify that the Delivery object is removed.

Conclusion

In this article, we learned about how to store JSON in an entity field using EF Core. We have talked about why we might sometimes need to store some fields of our entities in JSON columns and how to accomplish this using Entity Framework Core.