We had an existing application using ASP.NET MVC and EF6. We migrated to ASP.NET Core and EF Core 7. As title suggests, we are having trouble with existing queries where DayOfWeek is used. DayOfWeek cannot be translated to equivalent SQL query by EF Core. I cannot modify the code/query as the LINQ queries are generated by DevExtreme Dashboards and they said they cannot fix their code at the moment.
In dashboard we use date fields and assign grouping interval to those which can be DayOfWeek, Date-Hour, etc. The LINQ translation for these functions are not compatible with the EF Core. And as we dont have access to their code, it's not possible for us to modify how it's generating these queries.
On further investigation I ended up reading on QueryExpressionInterceptor in EF Core 7. Now my issue is, how do I translate all usage of DayOfWeek into something (DateDiff) that EF Core can translate to a SQL query.
Thanks to @Svyatoslav Danyliv I was able to resolve multiple issues.
I have implemented universal extension which replaces any Member or Method by appropriate pattern. It can be useful if you need to support other members translation.
Configure your db context in the following way with UseMemberReplacement and HasDbFunction. Sample has two replacements: DateTime.DayOfWeek and DateTime.AddHours.
DateTime.AddHours substitution solves issue with date conversions for SQL Server.
public class MyDbContext: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
// We add AddHours Method replacement to call function DayOfWeekImpl.
optionsBuilder.UseMemberReplacement((DateTime d) => d.DayOfWeek,
d => (DayOfWeek)(DayOfWeekImpl(d) - 1));
// We add DayOfWeek method replacement to call function DateAddHoursImpl.
optionsBuilder.UseMemberReplacement((DateTime d, double hours) => d.AddHours(hours),
(d, hours) => DateAddHoursImpl(d, hours));
}
static int DayOfWeekImpl(DateTime date) => (int)date.DayOfWeek;
static DateTime DateAddHoursImpl(DateTime date, double hours) => date.AddHours(hours);
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
... // other model configuring code
// register 'DayOfWeekImpl' translation code
modelBuilder.HasDbFunction(() => DayOfWeekImpl(default))
.HasTranslation(args =>
new SqlFunctionExpression("DATEPART",
new[] { new SqlFragmentExpression("weekday"), args[0] },
false,
new[] { false, false, false },
typeof(int),
null
));
// register 'DateAddHoursImpl' translation code
modelBuilder.HasDbFunction(() => DateAddHoursImpl(default, default))
.HasTranslation(args =>
new SqlFunctionExpression("DATEADD",
new[]
{
new SqlFragmentExpression("hour"), args[1], new SqlFunctionExpression("CONVERT",
new[] { new SqlFragmentExpression("datetime"), args[0] }, false,
new[] { true, false }, typeof(DateTime), RelationalTypeMapping.NullMapping)
},
false,
new[] { false, false, false },
typeof(DateTime),
null
));
}
}
Testing code:
var query = ctx.AnyTable
.Select(x => new
{
Date = DateTime.Today,
DayOfWeek = DateTime.Today.DayOfWeek,
HourPlus = DateTime.Today.AddHours(1),
});
var result = query.First();
if (result.Date.DayOfWeek != result.DayOfWeek)
throw new InvalidOperationException("Something wrong with 'DATEFIRST', check current setting in database 'SELECT @@DATEFIRST'");
It should generate the following SQL:
SELECT TOP(1)
CONVERT(date, GETDATE()) AS [Date],
CAST((DATEPART(weekday, CONVERT(date, GETDATE())) - 1) AS int) AS [DayOfWeek],
DATEADD(hour, 1.0E0, CONVERT(datetime, CONVERT(date, GETDATE()))) AS [HourPlus]
FROM [AnyTable] AS [e]
And extension implementation itself:
public static class EFCoreLinqExtensions
{
public static DbContextOptionsBuilder UseMemberReplacement<TValue>(this DbContextOptionsBuilder optionsBuilder, Expression<Func<TValue>> whatToReplace, Expression<Func<TValue>> replacement)
{
AddMemberReplacement(optionsBuilder, whatToReplace, replacement);
return optionsBuilder;
}
public static DbContextOptionsBuilder UseMemberReplacement<TObject, TValue>(this DbContextOptionsBuilder optionsBuilder, Expression<Func<TObject, TValue>> whatToReplace, Expression<Func<TObject, TValue>> replacement)
{
AddMemberReplacement(optionsBuilder, whatToReplace, replacement);
return optionsBuilder;
}
public static DbContextOptionsBuilder UseMemberReplacement<TParam1, TParam2, TResult>(this DbContextOptionsBuilder optionsBuilder,
Expression<Func<TParam1, TParam2, TResult>> whatToReplace,
Expression<Func<TParam1, TParam2, TResult>> replacement)
{
AddMemberReplacement(optionsBuilder, whatToReplace, replacement);
return optionsBuilder;
}
public static DbContextOptionsBuilder UseMemberReplacement<TParam1, TParam2, TParam3, TResult>(this DbContextOptionsBuilder optionsBuilder,
Expression<Func<TParam1, TParam2, TParam3, TResult>> whatToReplace,
Expression<Func<TParam1, TParam2, TParam3, TResult>> replacement)
{
AddMemberReplacement(optionsBuilder, whatToReplace, replacement);
return optionsBuilder;
}
static void AddMemberReplacement(DbContextOptionsBuilder optionsBuilder, LambdaExpression whatToReplace, LambdaExpression replacement)
{
var coreExtension = optionsBuilder.Options.GetExtension<CoreOptionsExtension>();
QueryExpressionReplacementInterceptor? currentInterceptor = null;
if (coreExtension.Interceptors != null)
{
currentInterceptor = coreExtension.Interceptors.OfType<QueryExpressionReplacementInterceptor>()
.FirstOrDefault();
}
if (currentInterceptor == null)
{
currentInterceptor = new QueryExpressionReplacementInterceptor();
optionsBuilder.AddInterceptors(currentInterceptor);
}
MemberInfo member;
if (whatToReplace.Body is MemberExpression memberExpression)
{
member = memberExpression.Member;
}
else
if (whatToReplace.Body is MethodCallExpression methodCallExpression)
{
member = methodCallExpression.Method;
}
else
throw new InvalidOperationException($"Expression '{whatToReplace.Body}' is not MemberExpression or MethodCallExpression");
if (whatToReplace.Parameters.Count != replacement.Parameters.Count)
throw new InvalidOperationException($"Replacement Lambda should have exact count of parameters as input expression '{whatToReplace.Parameters.Count}' but found {replacement.Parameters.Count}");
currentInterceptor.AddMemberReplacement(member, replacement);
}
class MemberReplacementVisitor : ExpressionVisitor
{
private readonly List<(MemberInfo member, LambdaExpression replacenment)> _replacements;
public MemberReplacementVisitor(List<(MemberInfo what, LambdaExpression replacenmet)> replacements)
{
_replacements = replacements;
}
protected override Expression VisitMember(MemberExpression node)
{
foreach (var (what, replacement) in _replacements)
{
if (node.Member == what)
{
var args = new List<Expression>();
if (node.Expression != null)
args.Add(node.Expression);
var visitor = new ReplacingExpressionVisitor(replacement.Parameters, args);
var newNode = visitor.Visit(replacement.Body);
return Visit(newNode);
}
}
return base.VisitMember(node);
}
protected override Expression VisitMethodCall(MethodCallExpression node)
{
foreach (var (what, replacement) in _replacements)
{
if (node.Method == what)
{
var args = new List<Expression>(node.Arguments);
if (node.Object != null)
args.Insert(0, node.Object);
var visitor = new ReplacingExpressionVisitor(replacement.Parameters, args);
var newNode = visitor.Visit(replacement.Body);
return Visit(newNode);
}
}
return base.VisitMethodCall(node);
}
}
sealed class QueryExpressionReplacementInterceptor : IQueryExpressionInterceptor
{
readonly List<(MemberInfo member, LambdaExpression replacenment)> _memberReplacements = new();
public Expression QueryCompilationStarting(Expression queryExpression, QueryExpressionEventData eventData)
{
if (_memberReplacements.Count == 0)
return queryExpression;
var visitor = new MemberReplacementVisitor(_memberReplacements);
var result = visitor.Visit(queryExpression);
return result;
}
public void AddMemberReplacement(MemberInfo member, LambdaExpression replacement)
{
_memberReplacements.Add((member, replacement));
}
}
}