通過動態傳遞GroupBy()創建查詢并使用運算式樹在Select()中創建新類

我有一個簡單的方法來構建 IQueryable 并回傳它。

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

這可以。我需要的是創建具有相同回傳型別的方法，但通過不同的屬性動態地分組。因此，從上面的代碼中，我想要 3 個動態部分，它們將作為引數傳遞：

ItemGroup = opportunity.OpportunityStage.Name

和

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

所以新的方法應該是這樣的

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

并這樣稱呼它：

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

所以主要是用這兩個選擇創建運算式：

            opportunity.OpportunityStage.Name,
            opportunity.OpportunityStage.Id

我已經嘗試過創建選擇運算式、groupby、Anonomoys 類和 DTO 類的創建，但我就是做錯了。

編輯：以下是所涉及的課程：

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

}

obj 類是一個相當大的類，所以我只發布它的一部分

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

uj5u.com熱心網友回復：

我已經簡化了您的分組查詢并引入了私有類IdName，它應該取代匿名類的使用：

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

標籤：C＃实体框架林克拉姆达表达式树

上一篇：如何根據可變所需數量對多組值進行分組（C#）

下一篇：按具有特定子字串的字串串列分組