如何将带时区星期判断的SQL查询转换为EF Core可用的LINQ查询

报错原因 #

你直接在LINQ语句中使用SQL原生函数SUBSTRING()、DATENAME()的写法不符合C#语法规范，EF Core无法将未映射的C#方法转换为对应的SQL语句，因此会抛出函数不存在的错误。

方案1：直接复用已有SQL（最稳妥） #

直接用EF Core提供的FromSqlRaw方法执行已经验证可用的原生SQL，无需修改原有逻辑：

var settings_data = _context.Settings
    .FromSqlRaw(@"SELECT TOP 1 [EndTime],[StartTime],[OrderDay]
                  FROM [dbo].[Settings]
                  WHERE SUBSTRING(DATENAME(weekday, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 0, 4) = OrderDay")
    .FirstOrDefault();

方案2：C#侧预计算星期缩写（兼容性最好） #

把星期缩写的计算逻辑放到C#侧完成，再将结果作为参数传入LINQ查询，完全规避数据库函数映射问题：

// 加载东部时区配置
TimeZoneInfo estZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
// 计算当前东部时区时间
DateTime currentEstTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, estZone);
// 获取3位英文星期缩写，和SQL返回格式对齐
string weekDayAbbr = currentEstTime.ToString("ddd");
// 执行标准LINQ查询
var settings_data = _context.Settings
    .Where(s => s.OrderDay == weekDayAbbr)
    .FirstOrDefault();

注意：请确保你的SQL实例的系统语言为英文，避免两边返回的星期缩写格式不匹配。

方案3：用EF Core映射函数改写LINQ #

如果需要完全用LINQ实现且逻辑在SQL侧执行，可以使用EF Core内置的函数映射方法：

var settings_data = (from s in _context.Settings
                     where EF.Functions.Substring(
                         EF.Functions.DateName("weekday", 
                             EF.Functions.AtTimeZone(EF.Functions.AtTimeZone(EF.Functions.GetDate(), "UTC"), "Eastern Standard Time")
                         ), 0, 4) == s.OrderDay
                     select s).FirstOrDefault();

注意：该方法需要EF Core 3.0及以上版本支持AtTimeZone方法映射。

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