How to Dynamically create Expression<Func<>> for count and group by clause

Question

I need to create dynamic lambda expression predicate for below query to query in Cosmos DB.

*select City, COUNT(City) as CityCount FROM Cities where status="Active"
group by City*

Earlier I have created for equal operation like this

var parameter = Expression.Parameter(typeof(T), "t");

MemberExpression expBody = Expression.Property(parameter, columnName);

But for getting Count and for Group By Clause how to do this.

Answer 1

I am not familiar with CosmoDB, but wouldn't LINQ be easier for this?

IQueryable<City> query 
    = cityContainer.GetItemLinqQueryable<City>()
                   .Where(city => city.Status == "Active")
                   .GroupBy(city => city);

using FeedIterator<City> iterator = query.ToFeedIterator<City>();

int cityCount = 0;

for (;iterator.HasMoreResults; cityCount++)
{
    City city = await setIterator.ReadNextAsync();
                    
    // do stuff
}

EDIT:

Examples to my answer in the comments ->

struct City
{
    public string Name { get; set; }
    public int Population { get; set; }
}

IQueryable<City> cities = default;

Func<City, bool> HasNastyName = c => c.Name.Length > 25;
Func<City, bool> IsMassive = c => c.Population > 500;

var nastyNameCities = cities.Where(HasNastyName);
var massiveCities   = cities.Where(IsMassive);

Taking the additional option to build these generically into account, you can build predicates quite dynamically with LINQ. I may be missing what you are trying to do, but I don't see any advantage in the usage of Expression Trees here.

The IQueryProvider<> of CosmoDB is already translating all the Queries for you, so unless I am unaware of something, you are writing unnecessary and possibly less optimized implementations by mapping Expressions to Queries yourself.

Answer 2

I believe that you can use dynamic Linq for this:

---

• https://dynamic-linq.net/
• https://www.nuget.org/packages/System.Linq.Dynamic.Core

There is a GroupBy extension that takes a string: https://dynamic-linq.net/basic-query-operators#GroupBy

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;

namespace ConsoleApp7
{
    class Program
    {
        static void Main(string[] args)
        {
            IQueryable<MyOb> testData = new EnumerableQuery<MyOb>(new List<MyOb>());
            var query = testData
                .Where(o => o.Active)
                .GroupBy("City")
                .Select("new (Key as City, Count() as CityCount)");

            Console.WriteLine("Hello World!");
        }
    }

    public class MyOb
    {
        public string City { get; set; }
        public bool Active { get; set; }
    }
}

The documentation of Dynamic Linq provides this example

var result = context.Posts.GroupBy("BlogId").Select("new(Key, Count() AS Count)");

Answer 3

Count and GroupBy are methods. So you first need to get the corresponding MethodInfos using reflection (take care to get the methods from the Queryable class, not Enumerable, and the correct overload!).

Then create a method call expression using Expression.Call(null, methodInfo, queryable). (The first parameter should be null since both methods are static.) Here, queryable is the IQueryable you already have built up to this point on which you want to call Count or GroupBy, respectively. For GroupBy, you also need to add an extra parameter, which is the lambda expression for projecting the City property - I assume you know how to build that.

---

Update

But if I understand your motivation correctly, you are actually trying to dynamically build an IQueryable with where-clauses, selects, groupby and/or counts, based on the base IQueryable you get from the library, is that correct?

In that case you shouldn't express Count and GroupBy as expressions. Rather, you should dynamically call the real methods Where, Select, Count and GroupBy on your base IQueryable (to get another IQueryable), but dynamically build lambda expressions for the parameters of these methods. In the end, you will have an IQueryable which the Cosmos DB LINQ provider can evaluate.