Perform CRUD operations on JSON data using SQL Server and EF Core -- Part 1

Non-relational data is quite common these days. Such a non-relational data is often stored in JSON format. There are database engines available that process JSON documents and allow you to work with data using specialized libraries designed for that purpose. However, at times you need to work with relational and non-relational data together in the same application.

In such cases rather than picking a non-relational database engine you would prefer storing non-relational data alongside relational data in the same database. Luckily, SQL Server allows you to store JSON data in a table's column. Moreover, you can query and process that JSON data using special functions designed for that purpose.

Entity Framework Core harnesses on this JSON support and provides the familiar programming model to work with JSON data. In this article we will discuss how CRUD operations can be performed on JSON data stored in a SQL Server table.

Let's assume that we want to store project data such as project name and team members in Projects table. To begin with we will create the Projects table in the Northwind database (or any other database of your choice) with the following schema.

As you can see, the Projects table consists of just three columns. The ProjectID column is an integer identity column. The ProjectName column is nvarchar(500) column that stores project's name. All the other details about the project such as project start date, end date, and team members are wrapped as JSON data and are stored in the ProjectInfo column. Note that ProjectInfo is nvarchar(MAX) column.

To get some idea as to how the ProjectInfo will be stored, take a look at the following sample JSON document.

{
    "BasicInfo" : {
        "StartDate" : "2023-01-20T10:15:00.000Z",
        "EndDate" : "2023-05-15T10:15:00.000Z",
        "Active" : true,
        "Notes" : "Sample notes"
    },

    "TeamMemberInfo" : [
    {
        "TeamMemberName" : "Team Member 1",
        "Email" : "member1@example.com",
    },
    {
        "TeamMemberName" : "Team Member 2",
        "Email" : "member2@example.com",
    },
    {
        "TeamMemberName" : "Team Member 3",
        "Email" : "member3@example.com",
    }]
}

As you can see, there are two properties -- BasicInfo and TeamMemberInfo. The BasicInfo property is an object that contains StartDate, EndDate, Active, and Notes properties. The StartDate and EndDate properties use ISO date format to store dates. The Active boolean property indicates whether a project is currently active state and holds true / false. The Notes property is a descriptive information about the project.

The TeamMemberInfo property is a JSON array. It can contain one or more team member objects each having TeamMMemberName and Email.

Now that you know how the JSON data is going to be stored, let's create EF Core classes that map to this JSON structure.

Create a new Web API project in Visual Studio. We create a Web API project so that we can wrap the CRUD operations in an API and quickly test the functionality using Swagger UI.

Once the project is created add new new folder named DataAccess under project's root and then add four classes namely -- BasicData, TeamMemberData, ProjectData, and Project.

The BasicData class maps to the "BasicInfo" section of the JSON document and is shown below:

public class BasicData
{
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public bool Active { get; set; }
    public string Notes { get; set; }
}    

On the same lines TeamMemberData class maps to the "TeamMemberInfo" section of the JSON document and is shown below:

public class TeamMemberData
{
    public string TeamMemberName{ get; set; }
    public string Email { get; set; }
}

Since BasicData and TeamMemberData will be stored in a ProjectInfo column of the Projects table, we create ProjectData class that wraps these two properties.

public class ProjectData
{
    public BasicData BasicInfo { get; set; }
    public List<TeamMemberData> TeamMemberInfo { get; set; }
}    

As you can see, the BasicInfo property is of type BasicData and TeamMemberInfo property is a List of TeamMemberData objects since there can be more than one team members.

Now that we have BasicData, TeamMemberData, and ProjectData classes ready, we can create the Project entity class as shown below:

[Table("Projects")]
public class Project
{
    [DatabaseGenerated
    (DatabaseGeneratedOption.Identity)]
    public int ProjectID { get; set; }
    public string ProjectName { get; set; }
    public ProjectData ProjectInfo { get; set; }
}    

The Project class us mapped to the Projects table using Table attribute. It has three properties namely ProjectID, ProjectName, and ProjectInfo. The ProjectID property is the identity integer value. The ProjectName is a string value and ProjectInfo is a ProjectData object as described above.

Next, add AppDbContext class to the DataAccess folder and add a custom DbContext as shown below:

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

    }

    public DbSet<Project> Projects { get; set; }

    protected override void 
    OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Project>()
            .OwnsOne(project => project.ProjectInfo, 
            ownedNavigationBuilder => 
            {
                ownedNavigationBuilder.ToJson();
                ownedNavigationBuilder.OwnsOne
                (projectdata => projectdata.BasicInfo);
                ownedNavigationBuilder.OwnsMany
                (projectdata => projectdata.TeamMemberInfo);
            }
        );
    }
}

The AppDbContext contains the Projects DbSet and also overrides the OnModelCreating() method. Notice the code shown in bold letters. That is where we configure that this property (ProjectInfo) should be persisted as a JSON column in the database -- ToJson(). You may read more details here.

You need to store the database connection in the appsettings.json file like this:

"ConnectionStrings": {
    "Northwind": "data source=.;
    initial catalog=Northwind;
    integrated security=true;
    Encrypt=False"
    }    

Make sure to change the connection string as per your database setup. Then register the AppDbContext in the Program.cs as shown below:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();

var connStr = builder.Configuration.
GetConnectionString("Northwind");
builder.Services.AddDbContext
<AppDbContext>(o=>o.UseSqlServer(connStr));

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

Notice the code shown in bold letters. You pick the connection string from the appsettings.json file using GetConnectionString() method. Then you pass this connection string to UseSqlServer() method.

Now that our database table and EF Core configuration is ready we can build a Web API that performs the CRUD operation on the Projects table. That's the subject of the next part of this article.

That's it for now! Keep coding!!