Taking a JSON array with file paths to update the JSON array for date last modified

Question

I currently have an API which I use dapper to connect to a SQL database to find the file path for documents related to a certain ID. The result is returned in JSON format as follows:

[{"DOC-ID": 1, "DOCUMENT_FULL_PATH": "/PATH/FILENAME.DOC"},{"DOC-ID": 2, "DOCUMENT_FULL_PATH": "/PATH/FILENAME2.DOC"}]

I am trying to get my API to deserialize the JSON data then link that too a model (I prefer to not use models but the only solution I found was using JSON DOM which is briefly discussed on MS website but does not provide an example of how to loop through the JSON array so I could not move forward with this example). When I try to deserialize the dapper query result I get the error indicated below (shown at the line item in the code). I am not sure what is triggering this as I would think the QuerySingle could be deserialized with this method. Once this error is fixed I need to check the files last modified date and save that value to the model which I then again need to serialize to send to the front end! I have spent so much time on this so some help would be much appreciated!

[HttpPost]
public ActionResult MainReviewDocuments([FromForm] string ID)
{
    //Using FormData on frontend
    //checking ID exists on searching in dashboard
    if (ID == null || ID.Length == 0)
    {
        return Ok(new { Result = "Failed" });
    }
    else
    {
        //We have parameters here just in case we want to use them
        var UserID = HttpContext.User.FindFirst(ClaimTypes.Name).Value;

        String query = "select dbo.A2Q_0132W_RR_IDDocumentation_JSON(@ID) as output";

        using (var connection = new SqlConnection(connectionString))
        {
            var json = connection.QuerySingle<string>(query, new { ID = ID}); 

            MainReviewDocuments? mainreviewdocuments = JsonSerializer.Deserialize<MainReviewDocuments>(json); // this line draws an error 'The JSON value could not be converted to Project.Models.MainReviewDocuments. Path: $ | LineNumber: 0 | BytePositionInLine: 1.'
            var rootPath = Path.Combine(Directory.GetParent(env.ContentRootPath).ToString(), "Files");

            foreach (var document in mainreviewdocuments)
            {
                filePath = Path.Combine(rootPath, document.DOCUMENT_FULL_PATH);
                //Check file system for each file path and set last modified value to model object LAST_MODIFIED. Struggling with this as well
            }
            return Ok(mainreviewdocuments); // Can I use Ok() method to convert model back to JSON?
        }
    }
}

Answer 1

In your original call, you need to de-serialize to a List:

MainReviewDocuments? mainreviewdocuments = JsonSerializer.Deserialize<List<MainReviewDocuments>>(json);

and then access your properties are required.

Using Newtonsoft.Json library:

You can de-serialize your JSON string that you receive from your DB into the following class:

public class MainReviewDocuments
{
    [JsonProperty("DOC-ID")]
    public int DOCID { get; set; }
    public string DOCUMENT_FULL_PATH { get; set; }
}

Or you can use dynamic to de-serialize your JSON:

var mainreviewdocuments = JsonSerializer.Deserialize<dynamic>(json);

and then access the properties as shown in the example below.

You can refer to a working example below:

using System;
using Newtonsoft.Json;
using System.Collections.Generic;
                    
public class Program
{
    public static void Main()
    {
        var myJsonString=@"[{'DOC-ID': 1, 'DOCUMENT_FULL_PATH': '/PATH/FILENAME.DOC'},{'DOC-ID': 2, 'DOCUMENT_FULL_PATH': '/PATH/FILENAME2.DOC'}]";
        var mainreviewdocuments =JsonConvert.DeserializeObject<List<MainReviewDocuments>>(myJsonString);
        Console.WriteLine("Example using Model: \n");
        foreach(var item in mainreviewdocuments)
        {
            Console.WriteLine(item.DOCID);
            Console.WriteLine(item.DOCUMENT_FULL_PATH);         
        }
        Console.WriteLine("\n");
        Console.WriteLine("Example using Dynamic: \n");
        
        //Example using dynamic
        var mainreviewdocumentsDynamic=JsonConvert.DeserializeObject<dynamic>(myJsonString);
        foreach(var item in mainreviewdocumentsDynamic)
        {
            Console.WriteLine(item["DOC-ID"]);
            Console.WriteLine(item["DOCUMENT_FULL_PATH"]);      
        }
    }
}

public class MainReviewDocuments
{
    [JsonProperty("DOC-ID")]
    public int DOCID { get; set; }
    public string DOCUMENT_FULL_PATH { get; set; }
}

Output:

Example using Model: 

1
/PATH/FILENAME.DOC
2
/PATH/FILENAME2.DOC


Example using Dynamic: 

1
/PATH/FILENAME.DOC
2
/PATH/FILENAME2.DOC

Using System.Text.Json library:

using System;
using System.Collections.Generic;
using System.Text.Json;
using System.Text.Json.Serialization;
                
public class Program
{
    public static void Main()
    {       
       var myJsonString="[{\"DOC-ID\": 1, \"DOCUMENT_FULL_PATH\": \"/PATH/FILENAME.DOC\"},{\"DOC-ID\": 2, \"DOCUMENT_FULL_PATH\": \"/PATH/FILENAME2.DOC\"}]";
       var mainreviewdocuments = JsonSerializer.Deserialize<List<MainReviewDocuments>>(myJsonString);
        Console.WriteLine("Example using Model: \n");
        foreach(var item in mainreviewdocuments)
        {
            Console.WriteLine(item.DOCID);
            Console.WriteLine(item.DOCUMENT_FULL_PATH);         
        }
        
        Console.WriteLine("\n");
        Console.WriteLine("Example using Dynamic: \n");
        using (JsonDocument document = JsonDocument.Parse(myJsonString))
        {
           foreach (JsonElement element in document.RootElement.EnumerateArray())
           {
               Console.WriteLine(element.GetProperty("DOC-ID"));
               Console.WriteLine(element.GetProperty("DOCUMENT_FULL_PATH"));
            }
        }
    }
}


public class MainReviewDocuments
{
    [JsonPropertyName("DOC-ID")]
    public int DOCID { get; set; }
    public string DOCUMENT_FULL_PATH { get; set; }
}

Output:

Example using Model: 

1
/PATH/FILENAME.DOC
2
/PATH/FILENAME2.DOC


Example using Dynamic: 

1
/PATH/FILENAME.DOC
2
/PATH/FILENAME2.DOC

Working example: https://dotnetfiddle.net/nEjPIK

You can read more on the comparison between the two libraries in this article