Entity Framework Core 7 JSON Columns | Mapping | Querying | Updating | Example

In this article, we are going to discuss about EF Core 7 feature that JSON data mapping, querying and updating operation.

JSON Document Case Study:

Here we can observe a 'Employee' table whose contact details are saved as document data. In general, we have to read it as string data in our dotnet application. But EF Core 7 supports that we can map the JSON document as a normal C# class and also we query them and also we can update the data inside of the document either fully or partially.

Fetch JSON As Simple String:

First, let's check the simple API endpoint which will fetch data directly which means our 'ContactDetails' column in the database is a simple string.

Our table class is like 'Employee.cs'.

Employee.cs:

public class Employee
{
    public int Id { get; set; }
    public string? FullName { get; set; }
    public string? ContactDetails { get; set; }
}

Our database context is like 'MyWorldDbContext.cs'.

MyWorldDbContext.cs:

using Dot7.JsonMap.Data.Entities;
using Microsoft.EntityFrameworkCore;

namespace Dot7.JsonMap.Data;

public class MyWorldDbContext : DbContext
{
    public MyWorldDbContext(DbContextOptions<MyWorldDbContext> context) : base(context)
    {

    }
    public DbSet<Employee> Employee { get; set; }
}

Our sample API endpoint.

[HttpGet]
[Route("employee-json-data")]
public async Task<IActionResult> GetEmployeeJSON()
{
	var result = await _myWorldDbContext.Employee.ToListAsync();
	return Ok(result);
}

JSON Column Mapping:

Now using EF Core 7 let's try to map the JSON document properties to the column.

ContactDetails Example JSON :

{
  "Contacts": {
    "Phone": "1234567890",
    "Email": "naveen@gmail.com"
  },
  "Addresses": [
    {
      "AddressType": "Current",
      "DNO": "13-42D",
      "City": "Hyderabad",
      "State": "Telangana",
      "Country": "India"
    },
    {
      "AddressType": "Permanent",
      "DNO": "2-20-b",
      "City": "Bhimavaram",
      "State": "Andhra Pradesh",
      "Country": "India"
    }
  ]
}

• Here we can observe 'Contacts' and 'Addresses' are child objects of our contact details data.

Let's create an entity like 'Contacts.cs'.

Contacts.cs:

public class Contacts
{
    public string? Phone { get; set; }
    public string? Email { get; set; }
}

Let's create an entity like 'Address.cs'.

Address.cs:

public class Address
{
    public string? AddressType{get;set;}
    public string? DNO{get;set;}
    public string? City{get;set;}
    public string? State{get;set;}
    public string? Country{get;set;}
}

Let's create an entity like 'ContactDetails.cs'

ContactDetails.cs:

public class ContactDetails
{
    public Contacts? Contacts{get;set;}
    public List<Address>? Addresses{get;set;}
}

Now in the 'Employee' entity change the type for 'ContactDetails' from 'string' to  'ContactDetails'(type we created above).

Employee.cs:

public class Employee
{
    public int Id { get; set; }
    public string? FullName { get; set; }
    public ContactDetails? ContactDetails { get; set; }
}

Now in our Database context model builder, we have to specify the types for our JSON data.

MyWorldDbConext.cs:

using Dot7.JsonMap.Data.Entities;
using Microsoft.EntityFrameworkCore;

namespace Dot7.JsonMap.Data;

public class MyWorldDbContext : DbContext
{
    public MyWorldDbContext(DbContextOptions<MyWorldDbContext> context) : base(context)
    {

    }
    public DbSet<Employee> Employee { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().OwnsOne(
            employee => employee.ContactDetails, ownedNavigationBuilder => {
                ownedNavigationBuilder.ToJson();
                ownedNavigationBuilder.OwnsOne(_ => _.Contacts);
                ownedNavigationBuilder.OwnsMany(_ => _.Addresses);
            }
        );
    }
}

• Here 'OwnsOne' & 'OwnsMany' methods are used for mapping the aggregate types.
• (Line: 17) Here defining that 'ContactDetails' will be the JSON data.
• (Line: 18) Defining the 'Contacts' will be a single object type using 'OwnsOne'
• (Line: 19) Defining the 'Addresses' will be collection object type using 'OwnsMany'.

Now if we observe the output of our endpoint then we can see as below.

Add Or Update JSON Document:

Let's implement an endpoint for adding the data to the JSON document in the database. 

[HttpPost]
[Route("add-adress")]
public async Task<IActionResult> AddAddress([FromQuery] int id, [FromBody] Address address)
{
	var result = await _myWorldDbContext.Employee.Where(_ => _.Id == id).FirstOrDefaultAsync();

	result?.ContactDetails?.Addresses?.Add(address);

	await _myWorldDbContext.SaveChangesAsync();
	return Ok();
}

• Here just fetch employee record and then add our new 'Address' to 'ContactDetails' and then invoke the 'SaveChangesAsync' method to update our data into the JSON document in the data base.

Let's implement an endpoint for updating the JSON document data in the database.

[HttpPost]
[Route("update-phone")]
public async Task<IActionResult> UpdatePhone([FromQuery] int id, [FromBody] Contacts contacts)
{
	var result = await _myWorldDbContext.Employee.Where(_ => _.Id == id).FirstOrDefaultAsync();

	if (!string.IsNullOrEmpty(result?.ContactDetails?.Contacts?.Phone))
	{
		result.ContactDetails.Contacts.Phone = contacts.Phone;
	}
	await _myWorldDbContext.SaveChangesAsync();
	return Ok();
}

Video Session:

Wrapping Up:

Hopefully, I think this article delivered some useful information on EF Core 7 features like JSON documents. I love to have your feedback, suggestions, and better techniques in the comment section below.

Follow Me:

YouTube

Facebook

Twitter

Pinterest