EF6 query translator does not support DateTime.TimeOfDay
, and Oracle provider does not support DbFunctions.CreateTime
and TimeSpan
parameters/constants.
Still there are some options before switching the storage from DateTime
to string
as suggested by another answer.
First, for equality checks you could compare the time components by either extracting them into separate variables (query parameters):
var hour = viewmodel.ExecutionTime.Hours;
var minute = viewmodel.ExecutionTime.Minutes;
var second = viewmodel.ExecutionTime.Seconds;
var db0010016 = _idb0010016Rep.FindBy(e =>
e.ExecutionTime.Hour == hour && e.ExecutionTime.Minute == minute && e.ExecutionTime.Second == second)
.FirstOrDefault();
or into fake DateTime
variable (queryParameter):
var executionTime = DateTime.Today + viewmodel.ExecutionTime;
var db0010016 = _idb0010016Rep.FindBy(e =>
e.ExecutionTime.Hour == executionTime.Hour && e.ExecutionTime.Minute == executionTime.Minute && e.ExecutionTime.Second == executionTime.Second)
.FirstOrDefault();
Second, you can work with time converted to seconds. This allows you also to perform any comparison:
var executionTime = (int)viewmodel.ExecutionTime.TotalSeconds;
var db0010016 = _idb0010016Rep.FindBy(e =>
60 * 60 * e.ExecutionTime.Hour + 60 * e.ExecutionTime.Minute + e.ExecutionTime.Second == executionTime)
.FirstOrDefault();
But doing all that manually is quite annoying and error prone. What I can offer is s small utility class providing custom extension method:
public static partial class QueryableExtensions
{
public static IQueryable<T> ConvertTimeSpans<T>(this IQueryable<T> source)
{
var expr = new TimeSpanConverter().Visit(source.Expression);
return source == expr ? source : source.Provider.CreateQuery<T>(expr);
}
class TimeSpanConverter : ExpressionVisitor
{
static readonly Expression<Func<DateTime, int>> ConvertTimeOfDay = dt =>
60 * (60 * dt.Hour + dt.Minute) + dt.Second;
static int ConvertTimespan(TimeSpan ts) =>
60 * (60 * ts.Hours + ts.Minutes) + ts.Seconds;
protected override Expression VisitMember(MemberExpression node)
{
if (node.Type == typeof(TimeSpan))
{
if (node.Member.DeclaringType == typeof(DateTime) && node.Member.Name == nameof(DateTime.TimeOfDay))
return ConvertTimeOfDay.ReplaceParameter(0, base.Visit(node.Expression));
// Evaluate the TimeSpan value, convert and wrap it into closure (to keep non const semantics)
return ConvertTimespan(base.VisitMember(node).Evaluate<TimeSpan>()).ToClosure().Body;
}
return base.VisitMember(node);
}
protected override Expression VisitBinary(BinaryExpression node)
{
if (node.Left.Type == typeof(TimeSpan))
return Expression.MakeBinary(node.NodeType, Visit(node.Left), Visit(node.Right));
return base.VisitBinary(node);
}
}
static T Evaluate<T>(this Expression source) => Expression.Lambda<Func<T>>(source).Compile().Invoke();
static Expression<Func<T>> ToClosure<T>(this T value) => () => value;
static Expression ReplaceParameter(this LambdaExpression source, int index, Expression target) =>
new ParameterReplacer { Source = source.Parameters[index], Target = target }.Visit(source.Body);
class ParameterReplacer : ExpressionVisitor
{
public ParameterExpression Source;
public Expression Target;
protected override Expression VisitParameter(ParameterExpression node) => node == Source ? Target : node;
}
}
It uses two small custom ExpressionVisitor
classes to convert the DateTime.TimeOfDay
property and TimeSpan
class properties similar to your viewModel.ExecutionTime
.
Now you should be use your original query like this:
var db0010016 = _idb0010016Rep.GetAll()
.Where(e => e.ExecutionTime.TimeOfDay == viewmodel.ExecutionTime)
.ConvertTimeStamps() // the magic happens here
.FirstOrDefault();
In case you want to use milliseconds instead of seconds, all you need is to change the first two statements in TimeSpanConverter
class as follows:
static readonly Expression<Func<DateTime, int>> ConvertTimeOfDay = dt =>
1000 * (60 * (60 * dt.Hour + dt.Minute) + dt.Second) + dt.Millisecond;
static int ConvertTimespan(TimeSpan ts) =>
1000 * (60 * (60 * ts.Hours + ts.Minutes) + ts.Seconds) + ts.Milliseconds;