C#: Dapper with JsonConvert.SerializeObject() not working properly

Question

I'm new to Newtonsoft.Json and Dapper.

I am executing an SQL query, and using the query's result I'm converting it to a JSON string to try to make it look like this:

{ "Orders" : [{"OrderID":10248, "Quantity":12}, {"OrderID":10343, "Quantity":4}, ...etc...]}

However when I run my C# code, my output looks completely different along with some unexpected additions:

[
  {
    "JSON_F52E2B61-18A1-11d1-B105-00805F49916B": "{\"Orders\":[{\"OrderID\":10248,\"Quantity\":12},{\"OrderID\":10248,\"Quantity\":10}{\"OrderID\":10271,\"Quantity\":24},{\"OrderID\":10272,\"Quantity\":6},{\"OrderID\":1027"
  },
  {
    "JSON_F52E2B61-18A1-11d1-B105-00805F49916B": "2,\"Quantity\":40},{\"OrderID\":10272,\"Quantity\":24}, ...etc... ]

As you can see I do not understand why it is adding the additional "JSON_F52E2B61-18A1-11d1-B105-00805F49916B". How do I remove these? How do I change my code to make it look like my desired output json string?

This is my code. I also made a fiddle with the incorrect output I'm getting https://dotnetfiddle.net/uWV6vs :

// Dapper Plus
// Doc: https://dapper-tutorial.net/query

// @nuget: Dapper -Version 1.60.6
using Newtonsoft.Json;   
using Dapper;
using System;
using System.Data.SqlClient;
                    
public class Program
{    
    public class OrderDetail
    {
        public int OrderDetailID { get; set; }
        public int OrderID { get; set; }
        public int ProductID { get; set; }
        public int Quantity { get; set; }
    }
    
    public static void Main()
    {
        string sql = "SELECT OrderID, Quantity FROM OrderDetails FOR JSON PATH, root ('Orders'), INCLUDE_NULL_VALUES";

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
        {   
            dynamic orderDetail = connection.Query(sql);
            //edit: the answer is to use connection.Query<string>, orderDetail[0]
            orderDetail = JsonConvert.SerializeObject(orderDetail,Formatting.Indented); 
            Console.WriteLine(orderDetail);     
        }
    }
}

Answer 1

I believe you don't need to request JSON from SQL, Dapper will parse results to the objects automatically

Removing "FOR JSON PATH, root ('Orders'), INCLUDE_NULL_VALUES" should help

string sql = "SELECT OrderID, Quantity FROM OrderDetails";

UPDATE:

sorry, keep updating the answer. This one gives you objects with the right structure and no extra backslashes

using Newtonsoft.Json;   
using Dapper;
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
                    
public class Program
{    
    public class OrderDetail
    {
        public int OrderDetailID { get; set; }
        public int OrderID { get; set; }
        public int ProductID { get; set; }
        public int Quantity { get; set; }
    }
    
    public class Result
    {
        public IEnumerable<OrderDetail> Orders { get; set; }
    }
    
    public static void Main()
    {
        string sql = "SELECT OrderID, Quantity FROM OrderDetails";

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
        {   
            var orderDetail = connection.Query<OrderDetail>(sql);
            var str = JsonConvert.SerializeObject(new Result { Orders = orderDetail },Formatting.Indented); 
            Console.WriteLine(str);     
        }
    }
}