将多分组+按年周筛选的T-SQL查询转换为LINQ方法语法

原T-SQL查询 #

select AppName, MethodName, sum(ElapsedMilliseconds)/count(MethodName) as avgMS
from [dbo].[AppMethodPerformance]
where YEAR(MethodDateTime) = 2023 and DatePart(week, MethodDateTime) = 4
group by appname, MethodName
order by AppName, MethodName

问题描述 #

需要将上述T-SQL转换为LINQ方法语法，尝试两种写法均未成功；同时自定义的ISOWeek方法无法在EF中使用（无法转换为SQL），需找到按年周筛选的替代方案。

用户尝试的代码：

List<AppMethodPerformance> allEntires = new();

using (var ctx = new AppLogContext(clientName))
{
    // 第一种尝试（已注释）
    //allEntires = await ctx.AppMethodPerformances
    //                        .Select(s => new { s.ElapsedMilliseconds, s.AppName, s.MethodName, s.MethodDateTime})
    //                        .Where(s => s.MethodDateTime.Year == year && ISOWeek(s.MethodDateTime) == week)
    //                        .GroupBy(s => new { s.AppName, s.MethodName, s.ElapsedMilliseconds } ).Sum(x => x.Key.ElapsedMilliseconds)
    //                        .Select(s => 
    //                                new AppMethodPerformance
    //                                {
    //                                    AppName = s.Key.AppName,
    //                                    MethodName = s.Key.MethodName,
    //                                    ElapsedMilliseconds = s.Key.ElapsedMilliseconds
    //                                })
    //                        .OrderBy(s => s.AppName).ThenBy(s => s.MethodName)
    //                        .ToListAsync();
    // 第二种尝试
    allEntires = await ctx.AppMethodPerformances
                            .Where(s => s.MethodDateTime.Year == year && ISOWeek(s.MethodDateTime) == week)
                            .GroupBy(s => new
                            {
                                s.AppName,
                                s.MethodName,
                                s.ElapsedMilliseconds
                            })
                            .Select(s => 
                                    new AppMethodPerformance 
                                    { 
                                        AppName = s.Key.AppName,
                                        MethodName = s.Key.MethodName,
                                        ElapsedMilliseconds = s.Key.ElapsedMilliseconds
                                    })
                            .ToListAsync();
}

参考的自定义ISOWeek方法：

private static int ISOWeek(DateTime methodDateTime)
{
    CultureInfo myCI = new CultureInfo("en-US");
    Calendar myCal = myCI.Calendar;

    // 获取GetWeekOfYear所需的DTFI属性
    CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule;
    DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek;

    return myCal.GetWeekOfYear(DateTime.Now, myCWR, myFirstDOW);
}

解决方案 #

一、正确的LINQ方法语法 #

核心错误在于分组键包含了ElapsedMilliseconds，导致无法按应用名和方法名聚合计算平均值。正确写法如下：

using (var ctx = new AppLogContext(clientName))
{
    var query = ctx.AppMethodPerformances
        .Where(s => s.MethodDateTime.Year == year && /* 周筛选条件，见下文 */)
        .GroupBy(s => new { s.AppName, s.MethodName })
        .Select(g => new AppMethodPerformance
        {
            AppName = g.Key.AppName,
            MethodName = g.Key.MethodName,
            ElapsedMilliseconds = g.Sum(item => item.ElapsedMilliseconds) / g.Count()
        })
        .OrderBy(s => s.AppName)
        .ThenBy(s => s.MethodName);

    allEntires = await query.ToListAsync();
}

这段代码会生成与原T-SQL逻辑完全一致的SQL，实现按应用名和方法名分组、计算平均耗时并排序。

二、EF中按年周筛选的替代方案 #

自定义方法无法被EF转换为SQL，需使用EF支持的内置函数或日期区间筛选：

方案1：使用EF内置DatePart函数（匹配原T-SQL逻辑）

EF Core 3.0+可直接使用EF.Functions.DatePart映射到SQL的DATEPART函数，完美匹配原T-SQL的DatePart(week, ...)：

.Where(s => s.MethodDateTime.Year == year && 
            EF.Functions.DatePart("week", s.MethodDateTime) == week)

若使用旧版EF（非Core），替换为DbFunctions.DatePart即可。

方案2：匹配自定义ISOWeek的美式周规则

你的自定义方法使用美式周规则（一周从周日开始，第一周包含1月1日），方案1的DatePart("week", ...)已经完全对应这个逻辑，无需额外调整。如果需要严格的ISO 8601周规则（一周从周一开始，第一周至少4天在当年），可改为：

.Where(s => s.MethodDateTime.Year == year && 
            EF.Functions.DatePart("iso_week", s.MethodDateTime) == week)

方案3：日期区间筛选（避免函数转换问题）

如果担心数据库函数兼容性，可预先计算目标周的起始和结束日期，直接按日期区间筛选：

// 计算美式周的起始和结束日期（一周从周日开始）
var firstDayOfYear = new DateTime(year, 1, 1);
int daysToFirstSunday = (int)DayOfWeek.Sunday - (int)firstDayOfYear.DayOfWeek;
if (daysToFirstSunday < 0) daysToFirstSunday += 7;
var firstSunday = firstDayOfYear.AddDays(daysToFirstSunday);
var targetWeekStart = firstSunday.AddDays((week - 1) * 7);
var targetWeekEnd = targetWeekStart.AddDays(6);

// 按日期区间筛选
.Where(s => s.MethodDateTime >= targetWeekStart && s.MethodDateTime <= targetWeekEnd)

内容的提问来源于stack exchange，提问作者Chuck