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

Question

I am using entity framework 6 with oracle and Sql. Timespan datatype is not working with oracle. so i changed datatype to datetime. now i want to compare only time in datetime with Linq query. ex.

var db0010016 = _idb0010016Rep.GetAll().Where(e => e.ExecutionTime.TimeOfDay == viewmodel.ExecutionTime).FirstOrDefault();

in above example e.ExecutionTime is datetime and viewmodel.ExecutionTime is timespan. i am using timeofday function to convert it to timespan

above query failed to execute so i used DbFunctions.CreateTime() function

var db0010016 = _idb0010016Rep.FindBy(e => DbFunctions.CreateTime(e.ExecutionTime.Hour, e.ExecutionTime.Minute, e.ExecutionTime.Second) == exetime).FirstOrDefault();

above ex exetime is timespan.still i am getting below error

{"Invalid parameter binding\r\nParameter name: ParameterName"}

Answer 1

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 (
[...]

Answer 2

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