Efcore: EFCore 3.0- SqlServer- Subtracting DateTime in LINQ does not generate correct sql.

[shvmgpt116]

I am running a simple application that uses Datetime type in the LINQ. It subtract two datetime in the select clause. The generated query seems to ignore the second datetime type and does not have anything generated for it. Here is my application-

TestApplication

```C#
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Debug;
using System.Linq;

namespace ConsoleApp61
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
try
{
using (var db = new MyContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
var result = db.Order.Where(o => o.OrderID < 10300).Select(o => (o.OrderDate.Value - new DateTime(1997, 1, 1))).ToList();
}
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
}
}

public class Order
{
public int OrderID { get; set; }
public DateTime? OrderDate { get; set; }
}

class MyContext : DbContext
{
public DbSet Order { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MySqlServerDB1;Integrated Security=True")
.UseLoggerFactory(_myLoggerFactory);
}
public static readonly LoggerFactory _myLoggerFactory = new LoggerFactory(new[] { new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider() });
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
}
}

Upon executing the LINQ the generated query is-
```sql
SELECT [o].[OrderDate]
FROM [Order] AS [o]
WHERE [o].[OrderID] < 10300

This does not seem correct as it ignores that a datetime is being subtracted. The generated query is equivalent to the following LINQ-
db.Order.Where(o => o.OrderID < 10300).Select(o => (o.OrderDate.Value)).ToList();

Not sure it this is an issue with EFCore or SqlServer provider for EFCore.

Steps to reproduce

Run the above-provided test case with Microsoft.EntityFrameworkCore.SqlServer.

Further technical details

EF Core version:Microsoft.EntityFrameworkCore 3.1.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.4.2

[ajcvickers]

@shvmgpt116 That calculation is happening on the client as part of the final projection. Are you getting incorrect results?

[shvmgpt116]

Thanks. I am getting the correct results.
I am trying to write an EFCore provider for Oracle.
Unlike SqlServer, it seems like Oracle DB allows datetime subtraction and hence client-side evaluation is not required.
If I try the same test with my Oracle EFCore provider which uses EFCore 3.0.0, I get following error-
"System.InvalidOperationException : No coercion operator is defined between types 'System.DateTime' and 'System.TimeSpan'."
Now if I try to use Oracle's official EFCore provider that is available on nuget (https://www.nuget.org/packages/Oracle.EntityFrameworkCore/), which uses EFCore 2.2.4 underneath, there is no error.
And the query that is generated is:

SELECT "o"."OrderDate" - TO_DATE('1997-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM "Orders" "o"
WHERE "o"."OrderID" < 10300

I am not sure if there is any infrastructure change in EFCore 3.0.0 which I need to incorporate in my Oracle EFCore provider.
Any clarification/help would be appreciated.

[ajcvickers]

@roji to look for relevant code in the Npgsql provider.

[roji]

@shvmgpt116 kudos on undertaking such a big task, good luck!

The Npgsql provider for PostgreSQL translates direct arithmetic on on DateTime and TimeSpan as you seem to want to do. There are a couple small things you need to do in order to make that happen - note that there were heavy changes in this area for EF Core 3.0, so the provider using 2.2 would look very different.

• Have a custom version of SqlExpressionFactory which knows about binary expressions on DateTime/TimeSpan. Here's the relevant fragment of code.
• If you also want to support subtracting two DateTimes (yielding a TimeSpan), you also need to override VisitBinary in your SqlTranslatingExpressionVisitor, here's the relevant fragment.

I don't think anything more is required; in general, you can look at other aspectsfor an example of how a 3.x-compatible provider works. Am going to close this now, but don't hesitate to post back with further questions.

[shvmgpt116]

@roji @ajcvickers Thanks a lot. That is exactly what was missing.