JSON Column In EF Core

In .Net 7 and above, EF Core supports JSON document columns and most of relational databases like SQL Server, PostgreSQL and MySQL support this feature. With this feature, we can access this JSON column data without serialization or deserialization. And we can use Linq for queries into JSON string data like filtering, sorting, projection, and other features of Linq. And EF Core maps .net type to a JSON document, and we can use a normal Linq query, which will translate the query in a JSON column. Using a JSON column in EF Core can help us store semi-structured data in a single column without creating multiple tables or columns for different data. And we have more flexibility to add or modify data without changing the database schema.

Mapping to JSON columns

Aggregate types are defined using OwnsOne and OwnsMany methods. OwnsOne can be used to map a single aggregate, and the OwnsMany method can be used to map a collection of aggregates. For example, let's take SupplierInformations as a aggregate type in our Product aggregate for storing products with supplier informations.

public record Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

public class SupplierInformation
{
    public string Name { get; set; }
    public Address Address { get; set; }
}

And here in the owner aggregate, we can store SupplierInformations of a product:

public class Product{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public IEnumerable<SupplierInformation>? SupplierInformations { get; set; } = new List<SupplierInformation>();
}

The Product aggregate is configured with OwnsMany, Because the Product aggregate has many SupplierInformation, and the aggregate type SupplierInformation configured with OwnsOne, Because it has one Address:

public sealed class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Product>(b =>
        {
            b.HasKey(x => x.Id);
            b.Property(x => x.Name).IsRequired();
            b.OwnsMany(x => x.SupplierInformations, ownedNavigationBuilder =>
            {
                ownedNavigationBuilder.ToJson(); // Stores the collection as a JSON column
                ownedNavigationBuilder.OwnsOne(s => s.Address); // Configure the owned type Address
            });
        });

        base.OnModelCreating(modelBuilder);
    }
}

After executing the ToJson() method in the configuration of Product aggregate, This aggregate now has a JSON string column SupplierInformations that contains a collection of SupplierInformation for each Product.

Queries in JSON columns 

Queries into JSON columns work just the same as querying into any entity type in EF Core, and we can easily use Linq query. Just here we don't need to use include keyword because we have whole date with relations in one place as a JSON string.

        // Retrieve the product by matching supplier country
        var result = await _dbContext.Products
            .Where(p => p.SupplierInformations!.Any(s => s.Address.Country == country)).ToListAsync();

Updating in JSON Columns

Update into JSON columns work just the same as updating into any entity type in EF Core like below:

        // Retrieve the product by its ID
        var product = _dbContext.Products.FirstOrDefault(p => p.Id == id);

        if (product != null)
        {
            product.Name = productRequestDto.Name;
            product.Description = productRequestDto.Description;
            product.SupplierInformations = productRequestDto.SupplierInformations.Adapt<ICollection<SupplierInformation>>();

            // Save the changes to the database
            _dbContext.Products.Update(product);
            await _dbContext.SaveChangesAsync();
        }

After configuring the relations and running the migration, we will have the product table with consist of SupplierInformations JSON column like below:

You can find the sample code in this repository:

🔗 https://github.com/meysamhadeli/blog-samples/tree/main/src/ef-core-json-column

Conclusion

Finally, JSON columns in EF Core can become a very powerful tool for dealing with semi-structured data models that are changing a lot. It would reduce the need for complex schema migrations and make it easier to store data. However, the simplicity of usage comes at the cost of other factors such as possible performance degradation, restricted querying capabilities, and problems related to data consistency and validation. JSON columns are therefore best for scenarios where flexibility overshadows everything else, and their benefits outweigh any potential drawbacks. For applications that require consistency in data, performance, and strong typing, the choice will probably still remain with the more traditional relational schema.

Reference

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#json-columns