I am trying to project a flat data source into an object that can be serialized directly to JSON using Newtonsoft.Json. I've created a small program in Linqpad with an imagined inventory overview as a test. The requested output is as follows:

• Site name
  • Inventory name
    • Product name
    • Weight
    • Units
  • Inventory name
    • Product (etc)

For the life of me I can't get it to only have a single "Site name" as the only root object. I want an to list the contents inside an inventory inside a site, but it always ends up looking like:

How can I make the "Site" distinct having a collection of "inventory" which each has a collection of "products"?

My actual data source is a database table and it resembles the structure of my test object - and it is what it is.

The test code in Linqpad: (note that it references Newtonsoft.Json)

void Main()
{
    var contents = new List<DatabaseRecord>()
    {
        new DatabaseRecord{Product="Autoblaster", Inventory="Hull 5", Site="Death star", Units=20,Weight=500},
        new DatabaseRecord{Product="E11 Blaster Rifle", Inventory="Hull 5", Site="Death star", Units=512,Weight=4096},
        new DatabaseRecord{Product="SWE/2 Sonic Rifle", Inventory="Hull 1", Site="Death star", Units=20,Weight=500},
        new DatabaseRecord{Product="Relby v10 Micro Grenade Launcher", Inventory="Hull 5", Site="Death star", Units=20,Weight=500},
        new DatabaseRecord{Product="T-8 Disruptor", Inventory="Hull 1", Site="Death star", Units=20,Weight=500},
        new DatabaseRecord{Product="E11 Blaster Rifle", Inventory="Hull 2", Site="Death star", Units=50,Weight=1200}
    };
    
    var inventorycontent = from row in contents
                    group row by row.Site into sites
                    orderby sites.Key
                    select from inventory in sites
                        group inventory by inventory.Inventory into inventories
                           orderby inventories.Key
                           select new
                           {
                               site = sites.Key,
                               inventory = inventories.Key,
                               lines = inventories.Select(i => new { i.Product, i.Weight, i.Units })
                           };

    contents.Dump();
    inventorycontent.Dump();
    
    JsonConvert.SerializeObject(inventorycontent, Newtonsoft.Json.Formatting.Indented).Dump();
}

// Define other methods and classes here
class DatabaseRecord
{
    public string Product { get; set; }
    public string Inventory { get; set; }
    public string Site { get; set; }
    public int Units { get; set; }
    public double Weight { get; set; }
    
}

JSON output:

[
  [
    {
      "site": "Death star",
      "inventory": "Hull 1",
      "lines": [
        {
          "Product": "SWE/2 Sonic Rifle",
          "Weight": 500.0,
          "Units": 20
        },
        {
          "Product": "T-8 Disruptor",
          "Weight": 500.0,
          "Units": 20
        }
      ]
    },
    {
      "site": "Death star",
      "inventory": "Hull 2",
      "lines": [
        {
          "Product": "E11 Blaster Rifle",
          "Weight": 1200.0,
          "Units": 50
        }
      ]
    },
    {
      "site": "Death star",
      "inventory": "Hull 5",
      "lines": [
        {
          "Product": "Autoblaster",
          "Weight": 500.0,
          "Units": 20
        },
        {
          "Product": "E11 Blaster Rifle",
          "Weight": 4096.0,
          "Units": 512
        },
        {
          "Product": "Relby v10 Micro Grenade Launcher",
          "Weight": 500.0,
          "Units": 20
        }
      ]
    }
  ]
]

Suggested correct output sample:

{
  "sites":[{
    "site": "Death star",
    "inventories":[
      {
        "name":"Hull 1",
        "lines":[{
          "Product": "SWE/2 Sonic Rifle",
          "Weight": 500.0,
          "Units": 20
        },
        {
          "Product": "T-8 Disruptor",
          "Weight": 500.0,
          "Units": 20
        }]
      },
      {
        "name":"Hull 2",
        "lines":[{
          "Product": "SWE/2 Sonic Rifle",
          "Weight": 500.0,
          "Units": 20
        }]
      }
      ]
    },
    {"site": "Other site",
    "inventories":[
      {
        "name":"Hull 1",
        "lines":[{
          "Product": "SWE/2 Sonic Rifle",
          "Weight": 500.0,
          "Units": 20
        }]
      }]
    }]
}

CodePudding user response：

Ok I have a solution using dictionaries which will group everything properly:

           //first get everything properly grouped with dictionaries
           var result = contents
            .GroupBy(x => x.Site)
            .ToDictionary(g => g.Key, g => g
                                .GroupBy(i => i.Inventory)
                                .ToDictionary(i => i.Key, i => i
                                        .Select(a => new 
                                            { 
                                                Product = a.Product, 
                                                Weight = a.Weight, 
                                                Units = a.Units 
                                            })
                                            .ToList()));

            //project to a new object that matches your desired json  
            var formattedResult = new
            {
                sites = (from r in result
                         select new
                         {
                             site = r.Key,
                             inventories = (from i in r.Value select new { name = i.Key, lines = i.Value }).ToList()
                         }).ToList()
            };

This is the output json:

{
   "sites": [
    {
      "site": "Death star",
      "inventories": [
        {
          "name": "Hull 5",
          "lines": [
            {
              "Product": "Autoblaster",
              "Weight": 500.0,
              "Units": 20
            },
            {
              "Product": "E11 Blaster Rifle",
              "Weight": 4096.0,
              "Units": 512
            },
            {
              "Product": "Relby v10 Micro Grenade Launcher",
              "Weight": 500.0,
              "Units": 20
            }
          ]
        },
        {
          "name": "Hull 1",
          "lines": [
            {
              "Product": "SWE/2 Sonic Rifle",
              "Weight": 500.0,
              "Units": 20
            },
            {
              "Product": "T-8 Disruptor",
              "Weight": 500.0,
              "Units": 20
            }
          ]
        },
        {
          "name": "Hull 2",
          "lines": [
            {
              "Product": "E11 Blaster Rifle",
              "Weight": 1200.0,
              "Units": 50
            }
          ]
        }
      ]
    }
  ]
}

As you can see I group by Site then make that into a dictionary which then I group by Inventory and make that into another dictionary with a list of products as values.

So basically the result is a

 Dictionary<string, Dictionary<string, List<Products>>>

The dictionary keys are Site, Inventory Name.

I use this GroupBy -->> Dictionary every time I have to build hierarchical json like this.