Performing sum on date subtraction (TimeSpan) not working (.NET 7)

[Karmgahl]

Example:

This will generate a "could not be translated" exception.

I find it strange that simple datetime operations like this is not at all working even in .NET 7. As it's something that is probably quite a common thing to do.

Sure one could always do the operation in .NET after the query has ran. But that is not a very performant solution, if you're fetching a lot of rows.

Also I'm wondering why oracle has chosen to not including ANY of the EF.Functions that Microsoft does for SQL server? If you would've, then this simple datetime handling would've probably worked without issue.

I really think microsoft did a major blunder in not enforcing all providers to provide some default EF.Functions as it makes becoming DB agnostic A LOT more complicated than it needs to be.

[alexkeh]

@Karmgahl Can you provide a simple, complete test case and/or a trace? That would help us diagnose the problem more quickly.

The ODP.NET EF.Functions support has been requested before, but not from a significant number of customers. Are there particular methods you'd want to prioritize to be supported?

[Karmgahl]

I think this is the best I can do for now.

(Ignore the timestamp ones as I was curious to see if using timestamp/datetimeoffset instead of date would change anything. It did not, same error)

Regarding EF.Functions. Id say the datediff functions included in the MS SQL provider that I illustrated in my screenshot for my previous post would've been nice to have. Especially with regard to the current "unable to translate" situation.

table in oracle:

  create table TBLENTITYDATETEST
  (
    id         NUMBER not null,
    startdate  DATE,
    enddate    DATE,
    startstamp TIMESTAMP(6) WITH TIME ZONE,
    endstamp   TIMESTAMP(6) WITH TIME ZONE,
    eventtype  NUMBER not null
  )

Inserts to fill with data

INSERT INTO tblentitydatetest (EventType, StartDate, EndDate, StartStamp, EndStamp) VALUES(1,
                                                                                           TO_DATE('2022-01-01 06:00:00','yyyy-mm-dd hh24:mi:ss'), 
                                                                                           TO_DATE('2022-01-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 05:00:00 UTC','yyyy-mm-dd hh24:mi:ss TZR'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 06:00:00 UTC','yyyy-mm-dd hh24:mi:ss TZR'));
                                                                                
INSERT INTO tblentitydatetest (EventType, StartDate, EndDate, StartStamp, EndStamp) VALUES(1,
                                                                                           TO_DATE('2022-01-01 09:00:00','yyyy-mm-dd hh24:mi:ss'), 
                                                                                           TO_DATE('2022-01-01 14:00:00','yyyy-mm-dd hh24:mi:ss'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 08:00:00 UTC','yyyy-mm-dd hh24:mi:ss TZR'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 13:00:00 UTC','yyyy-mm-dd hh24:mi:ss TZR'));
                                                                                
INSERT INTO tblentitydatetest (EventType, StartDate, EndDate, StartStamp, EndStamp) VALUES(2,
                                                                                           TO_DATE('2022-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss'), 
                                                                                           TO_DATE('2022-01-01 17:54:00','yyyy-mm-dd hh24:mi:ss'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 11:00:00 UTC','yyyy-mm-dd hh24:mi:ss TZR'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 16:54:00 UTC','yyyy-mm-dd hh24:mi:ss TZR'));
                                                                                           
INSERT INTO tblentitydatetest (EventType, StartDate, EndDate, StartStamp, EndStamp) VALUES(2,
                                                                                           TO_DATE('2022-01-01 18:23:50','yyyy-mm-dd hh24:mi:ss'), 
                                                                                           TO_DATE('2022-01-01 23:33:43','yyyy-mm-dd hh24:mi:ss'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 17:23:50 UTC','yyyy-mm-dd hh24:mi:ss TZR'),
                                                                                           TO_TIMESTAMP_TZ('2022-01-01 22:33:43 UTC','yyyy-mm-dd hh24:mi:ss TZR'));

entity:

public partial class TBLENTITYDATETEST : BaseEntity
{
    public int ID { get; set; }
    public int EVENTTYPE { get; set; }
    public DateTime? STARTDATE { get; set; }
    public DateTime? ENDDATE { get; set; }
    public DateTimeOffset? STARTSTAMP { get; set; }
    public DateTimeOffset? ENDSTAMP { get; set; }
}

public class TBLENTITYDATETESTConfiguration : CustomEntityConfiguration<TBLENTITYDATETEST>
{
    public override void Configure(EntityTypeBuilder<TBLENTITYDATETEST> builder)
    {
        builder.ToTable("TBLENTITYDATETEST", "TAK");
        builder.HasKey(t => t.ID);
        builder.Property(t => t.ID)
               .ValueGeneratedOnAdd();
        base.Configure(builder);
    }
}

Actual C# test code (.NET 7 and EF 7 with oracle EF 7 provider):

using TestContext db = new();

var basicSumQuery = from t in db.TBLENTITYDATETEST
				   select new
				   {
					EventType = t.EVENTTYPE,
					StartDate = t.STARTDATE,
					EndDate = t.ENDDATE,
					TotalHours = (t.ENDDATE - t.STARTDATE!)!.Value.TotalHours,
					TotalHoursUTC = (t.ENDSTAMP - t.STARTSTAMP!)!.Value.TotalHours
				   };
					
basicSumQuery.ToList(); //Works, because i used no sum or groupby here

basicSumQuery.Sum(t => t.TotalHours); //Will crash with "could not be translated"
			 
var basicGroupQuery = from t in db.TBLENTITYDATETEST
				       group t by t.EVENTTYPE into g
				       select new
				       {
						  EventType = g.Key,
						  TotalHours = g.Sum(s => (s.ENDDATE - s.STARTDATE!)!.Value.TotalHours),
						  TotalHoursUTC = g.Sum(s => (s.ENDSTAMP - s.STARTSTAMP!)!.Value.TotalHours)
				       };
					  
basicGroupQuery.ToList(); //Will crash with "could not be translated"

[alexkeh]

@Karmgahl Thanks for the complete test case. I was able to reproduce your issue. I've filed a bug (34996747) to track the issue.

The bug also occurs in the new Oracle EF Core 7.21.9, which was released last night. So, it will have to be fixed in a later release.