using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.Serialization.Formatters;

using System.IO;

using System.Data;

using System.ComponentModel.DataAnnotations;

using System.Globalization;

using System.Reflection;

​

using Newtonsoft.Json;

using Newtonsoft.Json.Linq;

using Newtonsoft.Json.Converters;

using Newtonsoft.Json.Serialization;    

​

using NUnit.Framework; // Throws NUnit.Framework.AssertionException

​

//https://stackoverflow.com/questions/58440439/creating-nested-json-with-data-from-sqldatareader

​

    public static class DataReaderExtensions

    {

        // Adapted from this answer https://stackoverflow.com/a/1202973

        // To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results

        // By https://stackoverflow.com/users/3043/joel-coehoorn

        public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)

        {

            while (reader.Read())

            {

                yield return select(reader);

            }

        }

    }

​

    class TestClass

    {

        public static void Test()

        {

            var cmd = JsonConvert.DeserializeObject<DataTable>(GetInputJson());

            using (IDataReader reader = cmd.ExecuteReader())

            {

                var query = reader

                    .SelectRows(r => 

                                new

                                {

                                    id = r.GetInt64(r.GetOrdinal("pid")),

                                    name = r["contributor"].ToString(),

                                    project = new {id = r.GetInt64(r.GetOrdinal("projectID")), name = r["projectName"].ToString() },

                                }

                               )

                    .GroupBy(r => new { r.id, r.name })

                    .Select(g => new { g.Key.id, g.Key.name, projects = g.Select(i => i.project) });

                var json = JsonConvert.SerializeObject(new { contributors = query }, Formatting.Indented);

                Console.WriteLine(json);

                Assert.IsTrue(JToken.DeepEquals(JToken.Parse(json), JToken.Parse(GetRequiredJson())));

            }

        }   

​

        static string GetInputJson()

        {

            var json = @"[

  {'pid': 1, 'contributor': 'jdalton', 'projectID': 1, 'projectName': 'lodash'},

  {'pid': 1, 'contributor': 'jdalton', 'projectID': 2, 'projectName': 'docdown'},

  {'pid': 1, 'contributor': 'jdalton', 'projectID': 3, 'projectName': 'lodash-cli'},

  {'pid': 2, 'contributor': 'contra',  'projectID': 4, 'projectName': 'gulp'},

  {'pid': 3, 'contributor': 'phated',  'projectID': 4, 'projectName': 'gulp'},

]

";

            return json;

        }

        static string GetRequiredJson()

        {

            var json = @"{

  ""contributors"": [

    {

      ""id"": 1,

      ""name"": ""jdalton"",

      ""projects"": [

        {

          ""id"": 1,

          ""name"": ""lodash""

        },

        {

          ""id"": 2,

          ""name"": ""docdown""

        },

        {

          ""id"": 3,

          ""name"": ""lodash-cli""

        }

      ]

    },

    {

      ""id"": 2,

      ""name"": ""contra"",

      ""projects"": [

        {

          ""id"": 4,

          ""name"": ""gulp""

        }

      ]

    },

    {

      ""id"": 3,

      ""name"": ""phated"",

      ""projects"": [

        {

          ""id"": 4,

          ""name"": ""gulp""

        }

      ]

    }

  ]

}";

            return json;

        }

    }

​

    static class TestExtensions

    {

        public static IDataReader ExecuteReader(this DataTable table)

        {

            return table.CreateDataReader();

        }

    }

​

    public class Program

    {

        public static void Main()

        {

            Console.WriteLine("Environment version: {0} ({1})", System.Runtime.InteropServices.RuntimeInformation.FrameworkDescription , GetNetCoreVersion());

            Console.WriteLine("Json.NET version: " + typeof(JsonSerializer).Assembly.FullName);

            Console.WriteLine();

​

            try

            {

                TestClass.Test();

            }

            catch (Exception ex)

            {

                Console.WriteLine("Failed with unhandled exception: ");

                Console.WriteLine(ex);

                throw;

            }

        }

        public static string GetNetCoreVersion()

        {

            //https://techblog.dorogin.com/how-to-detect-net-core-version-in-runtime-ecd65ad695be

            //

            var assembly = typeof(System.Runtime.GCSettings).GetTypeInfo().Assembly;

            var assemblyPath = assembly.CodeBase.Split(new[] { '/', '\\' }, StringSplitOptions.RemoveEmptyEntries);

            int netCoreAppIndex = Array.IndexOf(assemblyPath, "Microsoft.NETCore.App");

            if (netCoreAppIndex > 0 && netCoreAppIndex < assemblyPath.Length - 2)

                return assemblyPath[netCoreAppIndex + 1];

            return null;

        }

    }

​