Group by Dynamic Aggregate

Question

In the code below I want to replace x.BaseSalary with any other property whose name is stored in feildtoretrive:

var feildtoretrive  = "BaseSalary"
var groupcal = db.Employees.GroupBy(x=>x.Region)
                           .Select(group => new { 
                                    Key = group.Key, 
                                    Avg = group.Average(x => x.BaseSalary) 
                                                })
                           .ToList();

Answer 1

You need to

• create a MemberExpression that accesses that member of an instance
• compile a lambda expression that returns that member for a passed instance parameter

(I assume that the elements in Employees are of type Employee)

// the parameter expression for the lambda (your 'x')
var parameter = Expression.Parameter(typeof(Employee));
// the property access expression (your 'x.BaseSalary' or 'x.<feildtoretrive')
var propAccess = Expression.PropertyOrField(parameter, feildtoretrive);
// the build-together and compiled lambda
 var expression = (Expression<Func<Employee, int?>>)Expression.Lambda(propAccess, parameter);

You can now use lambda for the x.Average call:

new { Key = group.Key, Avg = group.Average(lambda) }

A caveat: This only works now for members of type int?. I'm lacking a little experience on how to do this more type independent, but what types can you calculate an average over? But if there are int or double members, another cast expression maybe necessary.

---

EDIT: (changed the return type to int?). According to Ivan Stoev's comment on my follow up question you could try this:

new { Key = group.Key, Avg = group.AsQueryable().Average(expression) }

EF6 should recognize the call to AsQueryable() and then use the correct Average method (note that I use the expression as argument instead of the lambda). EF Core and linq2Sql won't work with that.