Efcore: Expression DateTime.CompareTo drops entire Where

[wernerbarnard]

Calling .Where(predicate) on a DbSet where the predicate uses .CompareTo between a DateTime Entity property and DateTime.UtcNow causes the entire Where expression to be dropped and not included in the generated query.

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

```c#
var result = await context.DbSetName
.Where(s => s.ExpiryDate.CompareTo(now) > 0)
.Skip(0)
.Take(10)
.ToListAsync(cancellationToken);

Instead, the following works - is there any reason CompareTo is not supported, or why an exception is not thrown? (would be quite disasterous to unintentionally miss out on the entire expression if it's built based on conditions)

```c#
            var result = await context.DbSetName
                .Where(s => s.ExpiryDate > 0)
                .Skip(0)
                .Take(10)
                .ToListAsync(cancellationToken);

Further technical details

EF Core version: 2.1.0-rc1-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 x64
IDE: JetBrains Rider 2018.1

[pmiddleton]

Translation of DateTime.CompareTo is not currently supported. The query will use client side evaluation if it is enabled. See https://docs.microsoft.com/en-us/ef/core/querying/client-eval

In your case you can switch to this to get server evaluation.

.Where(s => s.ExpiryDate > now)

[wernerbarnard]

Hi @pmiddleton

Yeah - as I mentioned it's working by changing it to that.

The link you supplied, which shows "ConfigureWarnings" is great though - that answers my question around throwing warnings when EF is evaluating client-side.

Thanks!

[Muppets]

@ajcvickers @smitpatel I did some research into this. Currently, I can see we have a StringCompareTranslator which handles both Compare and CompareTo. If I expand that class out to also support DateTime, my tests seem to suggest it then generates the correct comparisons in the where clause.

This raises the question, if this is the right solution, what other types would we want to support while making this change? Taking a quick look at the .NET documentation, the CompareTo is supported on:

System.Guid.CompareTo(Object) Method
System.String.CompareTo(String) Method
System.Byte.CompareTo(Byte) Method
System.Int16.CompareTo(Int16) Method
System.Int32.CompareTo(Int32) Method
System.Int64.CompareTo(Int64) Method
System.Char.CompareTo(Object) Method
System.Single.CompareTo(Single) Method
System.Char.CompareTo(Char) Method
System.Guid.CompareTo(Guid) Method
System.Enum.CompareTo(Object) Method
System.Double.CompareTo(Double) Method
System.SByte.CompareTo(SByte) Method
System.UInt16.CompareTo(Object) Method
System.UInt64.CompareTo(UInt64) Method
System.UInt32.CompareTo(UInt32) Method
System.Boolean.CompareTo(Boolean) Method

Thoughts on direction here? Do you want to support Compare and CompareTo everywhere or just limit it to String and a select few other types? One translator class to rule them all?

[smitpatel]

One class to rule them all.

[pmiddleton]

@smitpatel - so you're going to add it to RelationalQueryModelVisitor ;)

[smitpatel]

May be RelationalQueryableExpressionVisitor 😏

[Muppets]

@smitpatel See how you like them apples...