How to convert plain object to nested sub object using Linq to SQL C#

I am working an application where I am receiving a plain list from database which I need to convert into the following JSON object. Here is what I have tried so far. I am not willing use nested foreach loops.

> JSON

{
  "Location": [
    {
      "LocationID": "123456",
      "DBSIdentifier": "EQUIP",
      "DBSVersion": "3221",
      "InterfaceVersion": "2.0",
      "LanguageCode": "EN",
      "CounterSale": [
        {
          "InvoiceNumber": "941041",
          "CreationTimestamp": "2018-04-02T07:03:18",
          "InvoiceTimestamp": "2018-04-02T07:03:18",
          "Part": [
            {
              "PartNumber": "AT194969",
              "Quantity": "1.00",
              "Description": "KEY",
              "DeerePart": "true",
              "MiscellaneousPart": "false",
              "PartsPerPackage": "1",
              "StockingLogicCode": "12A",
              "UnitofMeasure": "",
              "Warehouse": "D-1",
              "HistorySale": "N",
              "OnlineOrder": "",
              "PickListID": ""
            }
          ]
        }]
		}]
	}

> CODE

string connectionString = "data source=localhost;initial catalog=ASPENDV;integrated security=true;";
var result = new List<DataClass>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    result = connection.Query<DataClass>("sp_TestData", commandType: System.Data.CommandType.StoredProcedure).ToList();
}


#region [@ ##### ]


var obj = result.GroupBy(x => x.LocationId);

var root = obj.Select(x => new Root()
{
    Location = x.Select(l => new Location()
    {
        LocationID = l.LocationId,
        DBSIdentifier = l.DBSIdentifier,
        CounterSale = x.GroupBy(x => x.InvoiceNumber).Select(c => new CounterSale()
        {
            InvoiceNumber = l.InvoiceNumber,
            CreationTimestamp = l.CreationTimestamp,
            Part = x.Select(p => new Part()
            {
                PartNumber = p.PartNumber,
                Description = p.Description,
            }).ToList()
        }).ToList(),
    }).ToList(),
}); ;

#endregion

> MODELS

   public class DataClass
        {
            public string? LanguageCode { get; set; }
            public DateTime CreationTimestamp { get; set; }
            public string? DBSHEADERPARTSK { get; set; }
            public string? DBSIdentifier { get; set; }
            public int DBSVersion { get; set; }
            public string? Description { get; set; }
            public string? InvoiceNumber { get; set; }
            public Nullable<DateTime> InvoiceTimestamp { get; set; }
            public string? PartNumber { get; set; }
            public string? PartSerialNumber { get; set; }
            public int? PartPerPackage { get; set; }
            public int MfgId { get; set; }
            public int LocationId { get; set; }
        }

    public class CounterSale
    {
        public CounterSale()
        {
            this.Part = new List<Part>();
        }
        public string? InvoiceNumber { get; set; }
        public DateTime CreationTimestamp { get; set; }
        public DateTime InvoiceTimestamp { get; set; }
        public List<Part> Part { get; set; }
    }

    public class Location
    {
        public Location()
        {
            this.CounterSale = new List<CounterSale>();
        }
        public int LocationID { get; set; }
        public string? DBSIdentifier { get; set; }
        public string? DBSVersion { get; set; }
        public string? InterfaceVersion { get; set; }
        public string? LanguageCode { get; set; }
        public List<CounterSale> CounterSale { get; set; }
    }

    public class Part
    {
        public string? PartNumber { get; set; }
        public string? Quantity { get; set; }
        public string? Description { get; set; }
    }

    public class Root
    {
        public Root()
        {
            this.Location = new List<Location>();
        }
        public List<Location> Location { get; set; }
    }

My stored procedure returns data which I am binding into DataClass which I need to bind into Root Model as like JSON.

I am not able to bind the result into Root Model how can I do that ?

I suggest building a nested grouping from the bottom up by first grouping by location & invoice and then grouping again just by location. The resulting structure will similar to your desired structure, and it is then just a matter of traversing the groupings to build out your final nested data structure.

Something like:

List<DataClass> result; // No initialization needed here
using (SqlConnection connection = new SqlConnection(connectionString))
{
    result = connection.Query<DataClass>("sp_TestData", commandType: System.Data.CommandType.StoredProcedure).ToList();
}

// Grouping<Key=LocationId, Collection=Grouping<Key={LocationId, InvoiceNumber}, Collection=DataClass>
var nestedGroups = result
	.GroupBy(dat => new {dat.LocationId, dat.InvoiceNumber})
	.GroupBy(invGroup => invGroup.Key.LocationId)
	//??maybe?? .ToList()
	;

var root = new Root()
{
    Location = nestedGroups.Select(locGroup => new Location()
    {
        LocationID = locGroup.Key,
        DBSIdentifier = locGroup.First().First().DBSIdentifier,
        CounterSale = locGroup.Select(invGroup => new CounterSale()
        {
            InvoiceNumber = invGroup.Key.InvoiceNumber,
            CreationTimestamp = invGroup.First().CreationTimestamp,
            Part = invGroup.Select(dat => new Part()
            {
                PartNumber = dat.PartNumber,
                Description = dat.Description,
            }).ToList()
        }).ToList()
    }).ToList()
};

The .First() and .First().First() calls are used to reach down into the lowest level to retrieve values not included in the upper level grouping key. You could include DBSIdentifier and CreationTimestamp in the .GroupBy(...) selectors, but that would likely be less efficient than grouping using just the IDs.

See this .NET fiddle for a demo.

Sample results:

{
  "Location": [
    {
      "LocationID": 1,
      "DBSIdentifier": "11",
      "DBSVersion": null,
      "InterfaceVersion": null,
      "LanguageCode": null,
      "CounterSale": [
        {
          "InvoiceNumber": "201",
          "CreationTimestamp": "2023-05-11T03:18:33.9510984+00:00",
          "InvoiceTimestamp": "0001-01-01T00:00:00",
          "Part": [
            {
              "PartNumber": "301",
              "Quantity": null,
              "Description": "A"
            },
            {
              "PartNumber": "302",
              "Quantity": null,
              "Description": "B"
            }
          ]
        },
        {
          "InvoiceNumber": "202",
          "CreationTimestamp": "2023-05-11T03:18:33.9510984+00:00",
          "InvoiceTimestamp": "0001-01-01T00:00:00",
          "Part": [
            {
              "PartNumber": "303",
              "Quantity": null,
              "Description": "C"
            }
          ]
        }
      ]
    },
    {
      "LocationID": 2,
      "DBSIdentifier": "22",
      "DBSVersion": null,
      "InterfaceVersion": null,
      "LanguageCode": null,
      "CounterSale": [
        {
          "InvoiceNumber": "203",
          "CreationTimestamp": "2023-05-11T03:18:33.9510984+00:00",
          "InvoiceTimestamp": "0001-01-01T00:00:00",
          "Part": [
            {
              "PartNumber": "301",
              "Quantity": null,
              "Description": "A"
            }
          ]
        }
      ]
    }
  ]
}