我可以在实体框架中通过动态datePartArg使用SqlFunctions.DateDiff()吗？
我可以在实体框架中通过动态datePartArg使用SqlFunctions.DateDiff()吗？
提问于 2018-06-02 02:48:29
下面的代码因为注释行引发了EntityCommandCompilationException：
var datePartArg = "dd";
var minutesInStatePerSegment = await db.History_WorkPlaceStates
.Where(x => selector.StartTimeUtc <= x.Started && x.Ended < selector.EndTimeUtc)
.Select(x => new {
start = x.Started,
minutes = x.Minutes,
state = x.State,
})
.GroupBy(x => new {
//This causes an exception:
segment = SqlFunctions.DateDiff(datePartArg, selector.StartTimeUtc, x.start),
state = x.state,
})
.Select(x => new {
state = x.Key.state,
segment = x.Key.segment,
minutes = x.Sum(y => y.minutes),
}).ToListAsync();这是因为SQL Server中的DateDiff只能使用文字字符串作为其第一个参数，而不能使用变量。实体框架在SQL中生成一个变量，因此我们得到了异常。
有没有办法绕过这个问题？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-02 02:48:29
您可以创建以下扩展方法来解决此问题。在使用DateDiff时，只需将GroupBy函数替换为以下扩展方法：
public static IQueryable<IGrouping<TKey, TSource>> GroupByDateDiff<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector) {
var body = (NewExpression)keySelector.Body;
var transformedBodyArguments = body.Arguments.Select(arg => arg switch {
MethodCallExpression callNode
when callNode.Method.Name == "DateDiff" && callNode.Arguments[0].NodeType != ExpressionType.Constant
=> getTransformedDateDiffCall(callNode),
_ => arg,
}).ToArray();
var updatedExpr = keySelector.Update(body.Update(transformedBodyArguments), keySelector.Parameters);
return source.GroupBy(updatedExpr);
MethodCallExpression getTransformedDateDiffCall(MethodCallExpression dateDiffCallNode) {
var dateDiffFirstArg = dateDiffCallNode.Arguments[0];
if (dateDiffFirstArg.NodeType != ExpressionType.MemberAccess) {
throw new ArgumentException($"{nameof(GroupByDateDiff)} was unable to parse the datePartArg argument to the DateDiff function.");
}
var replacementExpression = Expression.Constant((string)GetMemberValue((MemberExpression)dateDiffFirstArg));
var alternativeArgs = dateDiffCallNode.Arguments.Skip(1).Prepend(replacementExpression).ToArray();
return dateDiffCallNode.Update(dateDiffCallNode.Object, alternativeArgs);
};
}
private static object GetMemberValue(MemberExpression member) {
var objectMember = Expression.Convert(member, typeof(object));
var getterLambda = Expression.Lambda<Func<object>>(objectMember);
var getter = getterLambda.Compile();
return getter();
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50649439
复制相关文章
相似问题
腾讯云开发者
