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

In the previous part of this article we created the Projects table and EF Core classes. Now it's time to create a Web API that performs the CRUD operations on the Projects table.

Begin by adding a new API Controller class called ProjectsController in the Controllers folder.

Then inject AppDbContext in the constructor as showq below:

[Route("api/[controller]")]
[ApiController]
public class ProjectsController : ControllerBase
{

    private AppDbContext db = null;

    public ProjectsController(AppDbContext db)
    {
        this.db = db;
    }
}    

Then add two GET methods that retrieve project data from the database.

[HttpGet]
public async Task<IActionResult> GetAsync()
{
    List<Project> data = 
    await db.Projects.ToListAsync();
    return Ok(data);
}


[HttpGet("{id}")]
public async Task<IActionResult> GetAsync(int id)
{
    Project proj = 
    await db.Projects.FindAsync(id);
    return Ok(proj);
}    

We won't go into the details of these actions since they are quite straightforward and you are probably already familiar with the EF Core methods such as ToListAsync() and FindAsync(). Both the actions wrap the data into the Ok() method so that HTTP status code us set to 200.

Then add PostAsync() action that inserts a new Project into the database.

[HttpPost]
public async Task<IActionResult> 
    PostAsync([FromBody] Project proj)
{
    await db.Projects.AddAsync(proj);
    await db.SaveChangesAsync();
    return CreatedAtAction
    ("Get", new { id = proj.ProjectID}, proj);
}    

The PostAsync() action accepts a Project object from the request body and attempts to insert it into the database using AddAsync() and SaveChangesAsync() methods. Then HTTP status code 201 is returned along with location header information using the CreatedAtAction() method.

The PutAsync() action that updates a Project is shown below:

[HttpPut("{id}")]
public async Task<IActionResult> 
PutAsync(int id, [FromBody] Project proj)
{
    var obj = db.Projects.Find(id);
    obj.ProjectName = proj.ProjectName;
    obj.ProjectInfo = proj.ProjectInfo;
    await db.SaveChangesAsync();
    return NoContent();
}    

The PutAsync() action received a ProjectID to be modified and a Project object with modified details. Inside, we find that Project and set the ProjectName and ProjectInfo properties to the modified project details. The changes are saved to the database using SaveChangesAsync(). This time we return HTTP status code 204 using NoContent() method.

Then add the DeleteAsync() action as shown below:

[HttpDelete("{id}")]
public async Task<IActionResult> DeleteAsync(int id)
{
    Project proj = await db.Projects.FindAsync(id);
    db.Projects.Remove(proj);
    await db.SaveChangesAsync();
    return NoContent();
}    

The DeleteAsync() simply deletes a project based from the database using Remove() and SaveChangesAsync() methods.

This completes the Projects Web API. We can now run and test our actions using Swagger interface. However, before you do that start SQL Server Profiler so that we can see the T-SQL queries generated by EF Core while performing the CRUD operations.

Now run the application to load the Swagger interface like this:

Since Projects table is currently empty, we will first insert a record into it. So, click on the Post button and insert a project using the following JSON data:

{
    "projectID": 0,
    "projectName": "Project 1",
    "projectInfo": {
        "basicInfo": {
        "startDate": "2023-05-05T06:14:04.523Z",
        "endDate": "2023-07-20T06:14:04.523Z",
        "active": true,
        "notes": "Project notes here"
        },
        "teamMemberInfo": [
        {
            "teamMemberName": "Team Member 1",
            "email": "member1@example.com"
        },
        {
            "teamMemberName": "Team Member 2",
            "email": "member2@example.com"
        }
        ]
    }
    }    

Upon clicking the Execute button your PostAsync() action will be invoked and the following T-SQL query will be executed on the database (see your SQL Profiler for this INSERT statement).

INSERT INTO [Projects] ([ProjectInfo], [ProjectName])
OUTPUT INSERTED.[ProjectID]
VALUES (@p0, @p1);
',N'@p0 nvarchar(283),@p1 nvarchar(4000)',
@p0=N'{"BasicInfo":{"Active":true,
"EndDate":"2023-07-20T06:14:04.523Z",
"Notes":"Project notes here",
"StartDate":"2023-05-05T06:14:04.523Z"},
"TeamMemberInfo":
[{"Email":"member1@example.com",
"TeamMemberName":"Team Member 1"},
{"Email":"member2@example.com",
"TeamMemberName":"Team Member 2"}]}',
@p1=N'Project 1'    

And you will find one record added to the Projects table.

As you can see, the ProjectInfo column stores data in JSON format. So, data from ProjectData object has been serialized in JSON format.

Now execute GetAsync() action using the Swagger interface. It will return this :

If you look at the SQL Profiler you will see this SELECT query being fired on the database:

SELECT [p].[ProjectID], 
[p].[ProjectName], 
JSON_QUERY([p].[ProjectInfo],'$')
FROM [Projects] AS [p]    

Notice the fragment marked in bold letters. EF Core uses JSON_QUERY() function of T-SQL to retrieve the JSON data stored in the database. You can read more about JSON_QUERY() function here.

On the similar lines you can try executing the GetAsync(id) action. This time the T-SQL query will be :

SELECT TOP(1) [p].[ProjectID], 
[p].[ProjectName], 
JSON_QUERY([p].[ProjectInfo],''$'')
FROM [Projects] AS [p]
WHERE [p].[ProjectID] = @__p_0',N'@__p_0 int',@__p_0=5    

Execute PutAsync() and DeleteAsync() actions using the Swagger interface and check the T-SQL queries. Here is a sample UPDATE query for your quick reference.

UPDATE [Projects] SET [ProjectInfo] = @p0, 
[ProjectName] = @p1
OUTPUT 1
WHERE [ProjectID] = @p2;
',N'@p0 nvarchar(284),
@p2 int,@p1 nvarchar(4000)',
@p0=N'{"BasicInfo":{"Active":false,
"EndDate":"2023-07-20T06:14:04.523Z",
"Notes":"Project notes here",
"StartDate":"2023-05-05T06:14:04.523Z"},
"TeamMemberInfo":[{"Email":"member1@example.com",
"TeamMemberName":"Team Member 1"},
{"Email":"member2@example.com",
"TeamMemberName":"Team Member 2"}]}',
@p2=5,@p1=N'Project 1'

After checking the CRUD operations, modify the GetAsync(id) action as shown below:

[HttpGet("{id}")]
public async Task<IActionResult> GetAsync(int id)
{
    Project proj = await db.Projects
        .Where(p => p.ProjectInfo
                        .BasicInfo
                        .Active == false)
        .FirstOrDefaultAsync();
    return Ok(proj);
}

Here, instead of using FindAsync() we use Where() and FirstOrDefaultAsync() to retrieve a project whose Active property is false.

This time the SQL Profiler will show the following T-SQL generated by EF Core:

SELECT TOP(1) [p].[ProjectID], 
[p].[ProjectName], 
JSON_QUERY([p].[ProjectInfo],'$')
FROM [Projects] AS [p]
WHERE CAST
(JSON_VALUE([p].[ProjectInfo],'$.BasicInfo.Active') 
AS bit) = CAST(0 AS bit)

You will find that this time the T-SQL makes use of JSON_QUERY() as well as JSON_VALUE() functions. You may read more about JSON_VALUE() function here.

So, in our example ToJson(), JSON_QUERY(), and JSON_VALUE() allowed us to work with JSON data stored in a SQL Server table.

That's it for now! Keep coding!!