Return to Answer

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]);   var unitKGs = new ExpandoObject() as IDictionary<string, Object>; unitKGs.Add("unit", "KGs"); foreach(var u in unitsNames) { var kgsSum=allUnits.Where(un => un["Name"].ToString() == u).Sum(_=>(int)_["KGs"]); unitKGs.Add(u, kgsSum); } var unitCartons = new ExpandoObject() as IDictionary<string, Object>; unitCartons.Add("unit", "Cartons"); foreach (var u in unitsNames) { var kgsSum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_["Cartons"]); unitCartons.Add(u, kgsSum); } var unitContainers = new ExpandoObject() as IDictionary<string, Object>; unitContainers.Add("unit", "Containers"); foreach (var u in unitsNames) { var kgsSum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_["Containers"]); unitContainers.Add(u, kgsSum); } List<ExpandoObject> res = new List<ExpandoObject>() { unitKGs as ExpandoObject,unitCartons as ExpandoObject,unitContainers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res); 

This is the result:

[ {"unit":"KGs","Item1":343,"Item2":1110}, {"unit":"Cartons","Item1":1262,"Item2":3032}, {"unit":"Containers","Item1":4,"Item2":36} ] 

I'm not saying this is not possible to do with a single Linq query, but I didn't see it that clear, perhaps someone smarter and/or with more available time might achieve it

EDIT

With some refactoring, this becomes:

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]); var kgs=GetExpandoObject(unitsNames, allUnits,"KGs"); var cartons = GetExpandoObject(unitsNames, allUnits, "Cartons"); var containers = GetExpandoObject(unitsNames, allUnits, "Containers"); List<ExpandoObject> res = new List<ExpandoObject>() { kgs as ExpandoObject,cartons as ExpandoObject,containers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res); 

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]); var kgs=GetExpandoObject(unitsNames, allUnits,"KGs"); var cartons = GetExpandoObject(unitsNames, allUnits, "Cartons"); var containers = GetExpandoObject(unitsNames, allUnits, "Containers"); List<ExpandoObject> res = new List<ExpandoObject>() { kgs as ExpandoObject,cartons as ExpandoObject,containers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res); 

This is the result:

[ {"unit":"KGs","Item1":343,"Item2":1110}, {"unit":"Cartons","Item1":1262,"Item2":3032}, {"unit":"Containers","Item1":4,"Item2":36} ] 

I'm not saying this is not possible to do with a single Linq query, but I didn't see it that clear, perhaps someone smarter and/or with more available time might achieve it

(You have to install and import MoreLinq in order to use DistinctBy)

EDIT

With some refactoring, this becomes:

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]); var kgs=GetExpandoObject(unitsNames, allUnits,"KGs"); var cartons = GetExpandoObject(unitsNames, allUnits, "Cartons"); var containers = GetExpandoObject(unitsNames, allUnits, "Containers"); List<ExpandoObject> res = new List<ExpandoObject>() { kgs as ExpandoObject,cartons as ExpandoObject,containers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res); 

...

 private static IDictionary<string, object> GetExpandoObject(IEnumerable<string> unitsNames, IEnumerable<JToken> allUnits, string concept) { var eo = new ExpandoObject() as IDictionary<string, Object>; eo.Add("unit", concept); foreach (var u in unitsNames) { var sum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_[concept]); eo.Add(u, sum); } return eo; } 

I don't know if you're going to laugh at this or what because it's so far away from what you where expecting, but the main problem I see in this code is that you're trying to transpose items in a collection (Item1, Item2) to properties in an object ("Item1": 4, "Item2": 36), that forces me to think about dynamic creation of objects by using ExpandoObject class:

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]); var unitKGs = new ExpandoObject() as IDictionary<string, Object>; unitKGs.Add("unit", "KGs"); foreach(var u in unitsNames) { var kgsSum=allUnits.Where(un => un["Name"].ToString() == u).Sum(_=>(int)_["KGs"]); unitKGs.Add(u, kgsSum); } var unitCartons = new ExpandoObject() as IDictionary<string, Object>; unitCartons.Add("unit", "Cartons"); foreach (var u in unitsNames) { var kgsSum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_["Cartons"]); unitCartons.Add(u, kgsSum); } var unitContainers = new ExpandoObject() as IDictionary<string, Object>; unitContainers.Add("unit", "Containers"); foreach (var u in unitsNames) { var kgsSum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_["Containers"]); unitContainers.Add(u, kgsSum); } List<ExpandoObject> res = new List<ExpandoObject>() { unitKGs as ExpandoObject,unitCartons as ExpandoObject,unitContainers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res); 

This is the result:

[ {"unit":"KGs","Item1":343,"Item2":1110}, {"unit":"Cartons","Item1":1262,"Item2":3032}, {"unit":"Containers","Item1":4,"Item2":36} ] 

I'm not saying this is not possible to do with a single Linq query, but I didn't see it that clear, perhaps someone smarter and/or with more available might achieve it

I don't know if you're going to laugh at this or what because it's so far away from what you where expecting, but the main problem I see in this code is that you're trying to transpose items in a collection (Item1, Item2) to properties in an object ("Item1": 4, "Item2": 36), that forces me to think about dynamic creation of objects by using ExpandoObject class:

var unitsNames = o["Assets"].SelectMany(a => a["Items"]).DistinctBy(i=>i["Name"]).Select(i=>i["Name"].ToString()); var allUnits=o["Assets"].SelectMany(a=>a["Items"]); var unitKGs = new ExpandoObject() as IDictionary<string, Object>; unitKGs.Add("unit", "KGs"); foreach(var u in unitsNames) { var kgsSum=allUnits.Where(un => un["Name"].ToString() == u).Sum(_=>(int)_["KGs"]); unitKGs.Add(u, kgsSum); } var unitCartons = new ExpandoObject() as IDictionary<string, Object>; unitCartons.Add("unit", "Cartons"); foreach (var u in unitsNames) { var kgsSum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_["Cartons"]); unitCartons.Add(u, kgsSum); } var unitContainers = new ExpandoObject() as IDictionary<string, Object>; unitContainers.Add("unit", "Containers"); foreach (var u in unitsNames) { var kgsSum = allUnits.Where(un => un["Name"].ToString() == u).Sum(_ => (int)_["Containers"]); unitContainers.Add(u, kgsSum); } List<ExpandoObject> res = new List<ExpandoObject>() { unitKGs as ExpandoObject,unitCartons as ExpandoObject,unitContainers as ExpandoObject }; string jsonString = JsonConvert.SerializeObject(res); 

This is the result:

[ {"unit":"KGs","Item1":343,"Item2":1110}, {"unit":"Cartons","Item1":1262,"Item2":3032}, {"unit":"Containers","Item1":4,"Item2":36} ] 

I'm not saying this is not possible to do with a single Linq query, but I didn't see it that clear, perhaps someone smarter and/or with more available time might achieve it