ASP.NET C# Web API: JsonConvert.SerializeObject Weirdly 'Escapes' Array-CodePudding

I am trying to build a simple GET API that will fetch data from an API and enable me to fetch it from my frontend (Javascript) however the problem that I am facing is when returning JsonConvert.SerializeObject it weirdly escapes an array that's stored in the database and is becoming a nightmare to parse in the frontend:

Code:

        public string Get()
        {
            sqlQuery =
                "SELECT TOP 60 * FROM tb_HandoverDetails ORDER BY SubmittedDateTimeUTC DESC";

            SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);

            DataTable dt = new DataTable();
            da.Fill (dt);
            if (dt.Rows.Count > 0)
            {
                return JsonConvert.SerializeObject(dt);
            }
            else
            {
                Response.StatusCode = 400;
                return "no data found";
            }
        }

Result:

[
  {
    "ID": 8,
    "Submitter": "auth.user",
    "SubmittedDateTimeUTC": "2021-10-05T20:29:13",
    "ExcelTableOne": "\"[{\\\"caseID\\\":[\\\"123\\\",\\\"1234\\\",\\\"12345\\\",\\\"123456\\\",\\\"1234567\\\",\\\"12345678\\\",\\\"123456789\\\"]},{\\\"owner\\\":[]},{\\\"assignee\\\":[]},{\\\"comments\\\":[]}]\""
  },
]

ExcelTableOne is the array that's weirdly escaped.

ExcelTableOne data inside Database:

"[{\"caseID\":[\"123\",\"1234\",\"12345\",\"123456\",\"1234567\",\"12345678\",\"123456789\"]},{\"owner\":[\"Ayush Lal\"]},{\"assignee\":[]},{\"comments\":[]}]"

Any ideas?

TIA

CodePudding user response：

You serialized again ExcelTableOne that was serialized already

To fix, try this

     var resultStr= Get();

   var resultPrev= JsonConvert.DeserializeObject<List<SubmiterStr>>(resultStr);

var result = resultPrev.Select(p => new Submiter {Id=p.Id, Submitter=p.Submitter, SubmittedDateTimeUtc=p.SubmittedDateTimeUtc}).ToList();

     for (int i = 0; i < result.Count; i  )
    {
        var excelTableStr = JsonConvert.DeserializeObject<string>(resultPrev[i].ExcelTableOneStr);
    
        result[i].ExcelTableOne= JsonConvert.DeserializeObject<List<ExcelTableOne>>(excelTableStr);
    
    }

classes

public partial class SubmiterStr
    {
    [JsonProperty("ID")]
    public long Id { get; set; }

    [JsonProperty("Submitter")]
    public string Submitter { get; set; }

    [JsonProperty("SubmittedDateTimeUTC")]
    public DateTimeOffset SubmittedDateTimeUtc { get; set; }

    [JsonProperty("ExcelTableOne")]
    public string ExcelTableOneStr { get; set; }
    
}
public partial class Submiter
{
    [JsonProperty("ID")]
    public long Id { get; set; }

    [JsonProperty("Submitter")]
    public string Submitter { get; set; }

    [JsonProperty("SubmittedDateTimeUTC")]
    public DateTimeOffset SubmittedDateTimeUtc { get; set; }

    [JsonProperty("ExcelTableOne")]
    public List<ExcelTableOne> ExcelTableOne { get; set; }
}

public partial class ExcelTableOne
{
    [JsonProperty("caseID", NullValueHandling = NullValueHandling.Ignore)]
    //[JsonConverter(typeof(DecodeArrayConverter))]
    public long[] CaseId { get; set; }

    [JsonProperty("owner", NullValueHandling = NullValueHandling.Ignore)]
    public object[] Owner { get; set; }

    [JsonProperty("assignee", NullValueHandling = NullValueHandling.Ignore)]
    public object[] Assignee { get; set; }

    [JsonProperty("comments", NullValueHandling = NullValueHandling.Ignore)]
    public object[] Comments { get; set; }
}

result

[
  {
    "ID": 8,
    "Submitter": "auth.user",
    "SubmittedDateTimeUTC": "2021-10-05T20:29:13-02:30",
    "ExcelTableOne": [
      {
        "caseID": [
          123,
          134,
          12345,
          123456,
          1234567,
          12345678,
          123456789
        ]
      },
      {
        "owner": []
      },
      {
        "assignee": []
      },
      {
        "comments": []
      }
    ]
  }
]