Create query by dynamically pass the GroupBy() and create new class in Select() using Expression tree

Question

I`m having simple method which builds IQueryable and returns it.

public IQueryable<ClassDTO> ReportByNestedProperty()
{
    IQueryable<Class> query = this.dbSet;
    
    IQueryable<ClassDTO> groupedQuery =
        from opportunity in query
        group new
        {
            ItemGroup = opportunity.OpportunityStage.Name,
            EstimatedRevenue = opportunity.EstimatedRevenue,
            CostOfLead = opportunity.CostOfLead
        }
        by new
        {
            opportunity.OpportunityStage.Name,
            opportunity.OpportunityStage.Id
        }
        into item
        select new ClassDTO()
        {
            ItemGroup = string.IsNullOrEmpty(item.Key.Name) ? "[Not Assigned]" : item.Key.Name,

            Count = item.Select(z => z.ItemGroup.Name).Count(), // int
            Commission = item.Sum(z => z.EstimatedRevenue), // decimal
            Cost = item.Sum(z => z.CostOfLead), // decimal?
        };

    return groupedQuery;
}

This is fine. The thing i need is to create method with same return type, but groupby by different prperties dynamically. So from the above code I want to have 3 dynamic parts which will be passed as params:

ItemGroup = opportunity.OpportunityStage.Name

and

        by new
    {
        opportunity.OpportunityStage.Name,
        opportunity.OpportunityStage.Id
    }

So the new method should be like this

    public IQueryable<ClassDTO> ReportByNestedProperty(string firstNestedGroupByProperty, string secondNestedGroupByProperty)
    {
        // TODO: ExpressionTree
    }

And call it like this:

ReportByNestedProperty("OpportunityStage.Name","OpportunityStage.Id")
ReportByNestedProperty("OtherNestedProperty.Name","OtherNestedProperty.Id")
ReportByNestedProperty("OpportunityStage.Name","OpportunityStage.Price")

So the main thing is to create expressions with these two selects:

            opportunity.OpportunityStage.Name,
            opportunity.OpportunityStage.Id

I have tried toe create the select expressions, groupby, the creation of Anonomoys classes and the DTO Class but I just cant get it right.

EDIT: Here are the classes involved:

public class ClassDTO
{
    public ClassDTO() { }

    [Key]
    public string ItemGroup { get; set; }

    public decimal Commission { get; set; }

    public decimal? Cost { get; set; }

    public int Count { get; set; }

}

Class obj is a pretty big one so i`m posting just part of it

public partial class Class
{
    public Class()  {   }

    [Key]
    public Guid Id { get; set; }
    public Guid? OpportunityStageId { get; set; }

    [ForeignKey(nameof(OpportunityStageId))]
    [InverseProperty(nameof(Entities.OpportunityStage.Class))]
    public virtual OpportunityStage OpportunityStage { get; set; }
}

public partial class OpportunityStage
{
    public OpportunityStage()
    {
        this.Classes = new HashSet<Class>();
    }

    [Key]
    public Guid Id { get; set; }

    public string Name { get; set; }

    [InverseProperty(nameof(Class.OpportunityStage))]
    public virtual ICollection<TruckingCompanyOpportunity> Classes{ get; set; }
}

Answer 1

I have simplified your Grouping query and introduced private class IdName which should replace anonymous class usage:

class IdName
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
}

static Expression MakePropPath(Expression objExpression, string path)
{
    return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
}

IQueryable<ClassDTO> ReportByNestedProperty(IQueryable<Class> query, string nameProperty, string idProperty)
{
    // Let compiler to do half of the work
    Expression<Func<Class, string, int, IdName>> keySelectorTemplate = (opportunity, name, id) =>
        new IdName { Name = name, Id = id };

    var param = keySelectorTemplate.Parameters[0];

    // generating expressions from prop path
    var nameExpr = MakePropPath(param, nameProperty);
    var idExpr = MakePropPath(param, idProperty);

    var body = keySelectorTemplate.Body;

    // substitute parameters
    body = ReplacingExpressionVisitor.Replace(keySelectorTemplate.Parameters[1], nameExpr, body);
    body = ReplacingExpressionVisitor.Replace(keySelectorTemplate.Parameters[2], idExpr, body);

    var keySelectorLambda = Expression.Lambda<Func<Class, IdName>>(body, param);

    // finalize query
    IQueryable<ClassDTO> groupedQuery = query
        .GroupBy(keySelectorLambda)
        .Select(item => new ClassDTO()
        {
            ItemGroup = string.IsNullOrEmpty(item.Key.Name) ? "[Not Assigned]" : item.Key.Name,
            Count = item.Count(x => x.Name), // int
            Commission = item.Sum(x => x.EstimatedRevenue), // decimal
            Cost = item.Sum(x => x.CostOfLead), // decimal?
        });

    return groupedQuery;
}