如何在没有 if 语句 (SUM/AVG) 的情况下实现以下查询

Question

public class DailyAggregator : Aggregator
{
    public override Dictionary<string, int?> Aggregate<T>(IQueryable<T> query, Expression<Func<T, DateTime>> groupByProperty, Expression<Func<T, double>> operationProperty = null)
    {
        if (operationProperty == null) // property of sum/avg (null = count)
            operationProperty = x => 1;
        if (_operationType.Equals(ReportAggregationOperation.Sum))
        {
            return query
                .GroupBy(g => new
                {
                    Day = TestableDbFunctions.TruncateTime(groupByProperty.Invoke(g))
                })
                .Select(x => new
                {
                    Key = x.Key.Day.ToString().Substring(0, 10),
                    Value = (int?) x.Sum(operationProperty.Compile()),
                })
                .ToDictionary(t => t.Key, t => t.Value);
        }
        else
        {
            return query
               .GroupBy(g => new
               {
                   Day = DbFunctions.TruncateTime(groupByProperty.Invoke(g))
               })
               .Select(
                x => new
                {
                    Key = x.Key.Day.ToString().Substring(0, 10),
                    Value = (int?) x.Average(operationProperty.Compile()),
                }).ToDictionary(t => t.Key, t => t.Value);
        }
    }
}

我正在使用 IOC 容器来创建每日聚合器/每月聚合器等实例...但是我无法通过表达式构建这个组，也无法应用正确的设计模式来消除上述 if 语句。

编译和调用函数来自 LinqKit 扩展。使用此聚合器的类正在查询数据库并收集呼叫中心报告的数据(例如，TotalCdrRecords报告，ReachCdrRecords报告，CdrTalkTime报告，CdrCallAfterworkTime等，CDR是呼叫数据记录，实际上保存有关呼叫的所有信息(。查询的类型 (T( 在报告类中指定，但基本上是一个 IReportableEntity，但当然 operationProperty，它可以是数据库实体的任何属性，我们可以执行计数/总和/平均操作，groupByProperty 始终是 DateTime 列。

它生成以下 sql 查询：

SELECT 
1 AS [C1], 
SUBSTRING(CASE WHEN ([GroupBy1].[K1] IS NULL) THEN N'' ELSE  CAST( [GroupBy1].[K1] AS nvarchar(max)) END, 0 + 1, 10) AS [C2], 
 CAST( [GroupBy1].[A1] AS int) AS [C3]
FROM ( SELECT 
    [Filter1].[K1] AS [K1], 
    SUM([Filter1].[A1]) AS [A1]
    FROM ( SELECT 
        convert (datetime2, convert(varchar(255), [Extent1].[CreatedOn], 102) ,  102) AS [K1], 
        cast(1 as float(53)) AS [A1]
        FROM [dbo].[VCCCdr] AS [Extent1]
        WHERE ([Extent1].[CreatedOn] >= @p__linq__0) AND ([Extent1].[CreatedOn] <= @p__linq__1) AND ([Extent1].[CompanyId] = @p__linq__2)
    )  AS [Filter1]
    GROUP BY [K1]
)  AS [GroupBy1]

Answer 1

由于您使用的是 LINQKit，因此可以提取用于调用Sum/Average 部分的表达式，并将其Invoke在查询中。

例如：

var aggregateFunc = _operationType.Equals(ReportAggregationOperation.Sum) ?
        Linq.Expr((IEnumerable<double> source) => source.Sum()) :
        Linq.Expr((IEnumerable<double> source) => source.Average());
return query
    .GroupBy(g => new
    {
        Day = DbFunctions.TruncateTime(groupByProperty.Invoke(g))
    })
    .Select(x => new
    {
        Key = x.Key.Day.ToString().Substring(0, 10),
        Value = (int?)aggregateFunc.Invoke(x.Select(operationProperty.Compile())),
    })
    .ToDictionary(t => t.Key, t => t.Value);