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?

your question has already basically been asked over here. – D30X May 02 '21 at 18:29 — D30X, May 02 '21 at 18:29

Nafis Islam · Accepted Answer · 2021-05-02T18:56:42.370

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

score 0 · Answer 2 · answered May 02 '21 at 19:20

You could leverage the JSON.Net types in your LINQ Query. JObject accepts an collection of JProperty and JArray 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:

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

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

2 Answers2