Json returned from SQL Server FOR JSON is different than whats returned from ASP.NET's serializer

Question

I have an ASP.NET API that returns a list of projects which works fine except I found that it runs slow due to creating a list of models which ASP.NET can use to serialize into json. If I remove code that creates the list of models and return json from sql server the API runs 10X faster (200 milliseconds vs 2 seconds). Below is the code that creates the list of models which ASP.NET serializes and works OK in the client app:

List<Project_Model> list = new List<Project_Model>();  
for (int i = 0; i < data.GetUpperBound(0) + 1; i++)  
{  
    //Pass data into the model's contructor  
    //Add a new instance of the model to a list  
    list.Add(new Project_Model(transactionId, (object[])data[i]));  
}  
  
//Return the list in an ASP.NET API method  
return Ok(list);  

and this is screenshot of what it looks like in Postman:

One project has text with special characters as you can see here

    {  
        "Pj_Id": "4cd50931-5567-4208-9866-056769d2b283",  
        "Pj_CUsr_Id": "9ba2a35e-8d9c-4ad7-9aef-516b3fd015c0",  
        "Pj_Number": "cxxx",  
        "Pj_Name": "znew project",  
        "Pj_Location": "xxxx. (:/?;-/:;()$&@“.,?!’.xxxx. (:/?;-/:;()$&@“.,?!’.  xxxx. (:/?;-/:;()$&@“.,?!’.  xxxx. (:/?;-/:;"  
    }  

However, when I return json from SQL Server to the client app, it crashes. The client app is a flutter app and I'm not a flutter develoer and therefore don't know exactly what's happening client side other than it crashes. That being said, we can see that the json returned from SQL Server looks different. Here's a screenshot from postman using the same API only it returns SQL Server's json:

and here's the json for the same project shown above:

{  
    \"Pj_Id\":\"4CD50931-5567-4208-9866-056769D2B283\",  
    \"Pj_CUsr_Id\":\"9BA2A35E-8D9C-4AD7-9AEF-516B3FD015C0\",  
    \"Pj_Number\":\"cxxx\",  
    \"Pj_Name\":\"znew project\",  
    \"Pj_Location\":\"xxxx. (:\/?;-\/:;()$&@“.,?!’.xxxx. (:\/?;-\/:;()$&@“.,?!’.  xxxx. (:\/?;-\/:;()$&@“.,?!’.  xxxx. (:\/?;-\/:;\"  
}  

Here's the c# code that returns SQL Server's json for the same API:

string JSONString = Project_DataServices.Project_GetListByFK_ForJson(transactionId, id);  
return Ok(JSONString);  

If I look at the same json from SQL Server in the debugger Watch window it removes the escape characters:

So I'm thinking rather than this being a SQL Server problem, I think its more like ASP.NET is adding the extra escape charactors in the return.

Any advice on what to do here so I can use the json from SQL Server? I'm sure it must be common to return json from SQL Server in APIs.

Thanks.

Answer 1

I found the answer.

To return Json from SQL Server, return it like this:

//Get json from sql server
string JSONString = Project_DataServices.Project_GetListByFK_ForJson(transactionId, id);

var response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new StringContent(JSONString);
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/json");
return response;

Note: I had to change the method return type from this:

public IHttpActionResult Project_GetListByFK...

To this:

public HttpResponseMessage Project_GetListByFK...