LINQ查询无法转换DateTime表达式引发异常，求解决方法

问题场景 #

执行以下LINQ查询时触发异常，推测是y.Duration <= (int)(DateTime.Now - targetDateTime).TotalMinutes语句导致：

string dateString = "2023-06-04 08:34:46.2743289";
DateTime targetDateTime = DateTime.ParseExact(dateString, "yyyy-MM-dd HH:mm:ss.fffffff", null);

var myUser =  _dbContext.Users
           .Where(u => u.Telephome== telephone)
           .Include(x => x.Activations)
           .FirstOrDefaultAsync(u => u.Activations.Any(y =>
                y.Code == code &&
                y.Duration <= (int)(DateTime.Now - targetDateTime).TotalMinutes));

抛出的异常 #

LINQ表达式'DbSet()
.Where(u0 => EF.Property<int?>(EntityShaperExpression:
Xchange.Core.Entities.User
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
, "Id") != null && object.Equals(
objA: (object)EF.Property<int?>(EntityShaperExpression:
Xchange.Core.Entities.User
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
, "Id"),
objB: (object)EF.Property<int?>(u0, "UserId")))
.Where(u0 => u0.Code == __code_1 && u0.Duration <= (int)(DateTime.Now - u0.TimeStamp).TotalMinutes)'无法被转换。请将查询改写为可转换的形式，或通过调用'AsEnumerable'、'AsAsyncEnumerable'、'ToList'或'ToListAsync'显式切换到客户端求值。

解决方法 #

EF Core无法将TotalMinutes的类型转换逻辑直接翻译成SQL语句。核心解决思路是提前在客户端计算好允许的分钟数，将其作为变量传入LINQ查询，这样EF Core就能正常解析表达式：

1. 先计算DateTime.Now - targetDateTime的总分钟数并转为int，赋值给变量
2. 在查询条件中直接使用该变量

修正后的代码 #

string dateString = "2023-06-04 08:34:46.2743289";
DateTime targetDateTime = DateTime.ParseExact(dateString, "yyyy-MM-dd HH:mm:ss.fffffff", null);
// 提前在客户端计算允许的分钟数
int allowedMinutes = (int)(DateTime.Now - targetDateTime).TotalMinutes;

var myUser = await _dbContext.Users
           .Where(u => u.Telephome == telephone)
           .Include(x => x.Activations)
           .FirstOrDefaultAsync(u => u.Activations.Any(y =>
                y.Code == code &&
                y.Duration <= allowedMinutes));

注：原代码缺少await关键字，修正时补上以符合异步方法使用规范。

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