Compare only time from datetime in entity framework 6 with odp.net Oracle 12c

Because of the date- and time-problems with oracle, we do it string-only:

using(MyDbContext ctx = new MyDbContext()){    TimeSpan myTime = new TimeSpan(12, 00, 00);    string myTimeString = myTime.ToString("hh':'mm':'ss");    List<ExecutionObjects> tmp = ctx.ExecutionObjects.Where(a => a.ExecutionTime.EndsWith(myTimeString)).ToList();    // Access field in source with seperated DateTime-property.    tmp.ForEach(e => Console.WriteLine(e.ExecutionTimeDateTime.ToShortDateString()));}

In the source you can add an DateTime-parsing-property:

public class ExecutionObject{    [Column("ColExecutionTime")]    public string ExecutionTime { get; set; }    [NotMapped]    public DateTime ExecutionTimeDateTime {        get        {            return DateTime.ParseExact(this.ExecutionTime, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);        }        set        {            this.ExecutionTime = value.ToString("yyyy-MM-dd HH:mm:ss");        }    }}

Not the most beautiful version, but working.

This is an Oracle-based problem in the DbFunctions. If you activate the sql-log you'll see that a function "CREATETIME()" is used, which is unknown.

Activate sql-log:ctx.Database.Log = Console.WriteLine;

The log will look like this:

SELECT *  FROM "ExecutionTimes" "Extent1" WHERE ((((CREATETIME (EXTRACT (HOUR FROM (CAST ([...]

if you want to compare only the time you can use the supported EDM canonical functions from DbFunctions (see here).Unfortunately DbFunction.CreateTime is not supported.

Still, if you are interested in a comparison for example at the level of seconds, you can do something like this:

        var refTime = new DateTime(2017, 12, 13, 09, 30, 31);        using (this.ctx = new MyContext())        {            var results = this.ctx.Groupings.Where(e => DbFunctions.DiffSeconds(e.EndDate, refTime) % 86400 == 0).ToList();        }

Here you take the difference in seconds using the EDM function DiffSeconds and make the modulus with the number of seconds in a day.

The query that gets executed is:

select  "Extent1"."GROUP_TYPE" as "GROUP_TYPE",  "Extent1"."GROUP_ENTITY_ID" as "GROUP_ENTITY_ID",  "Extent1"."ITEM_ENTITY_ID" AS "ITEM_ENTITY_ID",  "Extent1"."DATE_START" as "DATE_START",  "Extent1"."DATE_END" AS "DATE_END"from "MYSCHEMA"."ENTITY_GROUP_REL" "Extent1" where (0 = (mod( extract( day from ( cast(:p__linq__0 as timestamp(9)) -  cast("Extent1"."DATE_END" as timestamp(9))))*24*60*60 +  extract( hour from( cast(:p__linq__0 as timestamp(9)) -  cast("Extent1"."DATE_END" as timestamp(9))))*60*60 +  extract( minute from ( cast(:p__linq__0 as timestamp(9)) -  cast("Extent1"."DATE_END" as timestamp(9))))*60 +  extract( second from ( cast(:p__linq__0 as timestamp(9)) -  cast("Extent1"."DATE_END" as timestamp(9)))) ,86400)))

As you can see it translates correctly to the oracle functions server-side.

I hope it helps,

Nicola

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;