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

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

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);     
        }
    }
}