How to select and count base on a condition of another field using dynamic linq core

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 1

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;}
}

  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

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"}]