Question: I'm using *Entity Framework* and *Dynamic Linq Core* to perform some dynamic queries at run time. I have a question on how to write dynamic linq statements to output columns of counts where each column is a field item of another field.

Say I have a table with 3 columns: ID, Gender, and Age (assuming they are only in the 10s).

```
ID | Gender | Age
01 |   male |  20
02 | female |  30
... some thousands of rows

```

I would like to count the number of people in each gender (groupBy Gender), by their age group.

```
[{Gender:"male", "20": 120, "30": 200, "40": 300},
{Gender:"female", "20": 300, "30": 200, "40": 1000 }]

```

I tried to group by age, but this doesn't give exactly what i wanted in the above format, because each gender and age combo becomes a new array item.

```
var query = db.someDB
        .GroupBy("new(Gender, Age)")
        .Select("new(Key.Gender as Gender, Key.Age as Age, Count() as value)");

```

I'm restricted to use dynamic linq core because in my real application, the gender and age fields is up to the user to decide, so their field name will change at run-time.

How would you do it?

Answer: 
let's say your query returns a list of object of the following class

```
public class Data {
 public string Gender { get; set;}
 public int Age { get; set;}
 public int Value { get; set;}
}

```

```cs
  Data results = //query result

  var resultsV2 = results.GroupBy(r => r.Gender);

  var list = new List<IDictionary<string, object>>();

  foreach(var g in resultsV2)
  {
    IDictionary<string, object> obj = new ExpandoObject();

    var data = g.Select(x => x);
    obj["Gender"] = $"{g.Key}";

    foreach(var d in data)
    {
      var propName = $"{d.Age}";
      obj[propName] = $"{d.Value}";
    }

    list.Add(obj);

  }

  string jsonString = JsonConvert.SerializeObject(list);

```

[Fiddler](https://dotnetfiddle.net/rPMYN1)

input:

```
new List<Data>
{
  new Data() { Gender = "Male", Age = 20, Value = 5 },
  new Data() { Gender = "Male", Age = 30, Value = 3 },
  new Data() { Gender = "Female", Age = 20, Value = 9 }
};

```

output:

```
[{"Gender":"Male","20":"5","30":"3"},{"Gender":"Female","20":"9"}]

```
You could leverage the JSON.Net types in your LINQ Query. [JObject](https://www.newtonsoft.com/json/help/html/M_Newtonsoft_Json_Linq_JObject__ctor_3.htm) accepts an collection of [JProperty](https://www.newtonsoft.com/json/help/html/M_Newtonsoft_Json_Linq_JProperty__ctor_1.htm) and [JArray](https://www.newtonsoft.com/json/help/html/M_Newtonsoft_Json_Linq_JArray__ctor_3.htm) accepts an colection of JObject. Those can be constructed in one LINQ query:

```
var table = new List<Person>{
        new Person(30,"M"),
        new Person(30,"M"),
        new Person(30,"M"),
        new Person(20,"M"),
        new Person(40,"M"),
        new Person(30,"V"),
        new Person(50,"V"),
        new Person(50,"V")
    };

    var data = 
        from p1 in table
        group p1 by p1.Gender into genderGrp  
        // Build our JObject
        select new JObject( 
            new JProperty( "Gender", genderGrp.Key)
            // and the rest of proprties will the collection of Age
            , (  from p2 in genderGrp            // over the gender
                 group p2 by p2.Age into ageGrp
                 select new JProperty(
                     ageGrp.Key.ToString()  // needs to be a string
                   , ageGrp.Count()))       // aggregate count
       );

    var jtoken = new JArray(data);  // the collection of JObjects goes in the JArray
    Console.WriteLine(jtoken.ToString());

```

The result will be:

```json
[
  {
    "Gender": "M",
    "30": 3,
    "20": 1,
    "40": 1
  },
  {
    "Gender": "V",
    "30": 1,
    "50": 2
  }
]

```