Webapi: Comparing Date or DateTime properties with date constant.

[khodaie]

Comparing Date or DateTime properties with date constants throws the following exception:

No coercion operator is defined between types 'Microsoft.OData.Edm.Date' and 'System.Nullable`1[System.DateTimeOffset]'.

Assemblies affected

*Microsoft.AspNet.OData, Version=7.0.0.20530

Reproduce steps

1. Declare an entity with Date or DateTime SQL type. (for example: Operation with EffectiveDate property of type DateTime and Date SQL type)
2. Create OData endpoints for the entity.
3. Execute this OData query: ~/Operations?$filter=EffectiveDate eq 2018-06-01

Expected result

The sample query should return HTTP response 200 OK.

Actual result

The HTTP response is 400 Bad Request with this exception:

The query specified in the URI is not valid. No coercion operator is defined between types 'Microsoft.OData.Edm.Date' and 'System.Nullable`1[System.DateTimeOffset]'.

Additional detail

The Microsoft.AspNet.OData.Query.Expressions.ExpressionBinderBase.CreateConvertExpression(ConvertNode convertNode, Expression source) is responsible for this exception. Specifying the Date to DateTimeOffset conversion will fix it.

[khodaie]

Proposed fix: https://github.com/OData/WebApi/pull/1474

[xuzhg]

@khodaie Thank you for filing issue and sending us PR to fix. However, I am wondering what's your data source or DB?

[khodaie]

@xuzhg I created a sample data source:

``` C#
public class Operation
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

    public DateTime? OperationDate { get; set; }

    public DateTimeOffset? CreatedAt { get; set; }

    public TimeSpan? Duration { get; set; }
}

public class OperationContext : DbContext
{
    public virtual DbSet<Operation> Operations { get; set; }
}

The controller class:

``` C#
    public class OperationsController : ODataController
    {
        private readonly OperationContext _db = new OperationContext();

        [EnableQuery]
        public IQueryable<Operation> Get() => this._db.Operations;

        [EnableQuery]
        public SingleResult<Operation> Get([FromODataUri] int key) => SingleResult.Create(this._db.Operations.Where(p => p.Id == key));

        protected override void Dispose(bool disposing)
        {
            this._db.Dispose();
            base.Dispose(disposing);
        }
    }

And the WebApiConfig:

``` C#
var builder = new ODataConventionModelBuilder();
builder.EntitySet("Operations");
config.Filter();
config.MapODataServiceRoute("odata", null, builder.GetEdmModel());

The sample OData queries:
``` OData
http://localhost/Operations?$filter=OperationDate eq 2018-06-12
http://localhost/Operations?$filter=CreatedAt ge 2018-06-12

[xuzhg]

@khodaie I think you should use date() method.

For example:
C# http://localhost:11197/Operations?$filter=date(OperationDate) eq 2018-06-12 http://localhost:11197/Operations?$filter=date(CreatedAt) eq 2018-06-12

[xuzhg]

@khodaie Close it, please file new issue or re-open it if you have further concerns.

[ceouten]

I would prefer not to have to ask all my consumers to change how they are currently using my REST API because I want to upgrade an internal framework. Is there a way I can implement the proposed fix as a custom ExpressionBinder?

[NetTecture]

@AlanWong-MS @xuzhg This is a ridiculous fix, totally in violation of the odata standards. Literals do not need conversion methods.

At the end this is a problem of parsing literals. And there are way moer than that - guid's also do not work.

Soneone needs to write unit tests for the whole set in http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html and then sit down and program this functionality. 5.1.11.1 is quite clear how literal values for all data types have to look.

[erbaker]

I don't know if this is related or still an issue or not, but I'm seeing some strange behavior from parsing DateTime type in .net.

?$filter=IsActive eq true and IsAutomated eq true and website ne null and length(website) gt 0 and (LastUpdated eq null or date(LastUpdated) lt 2018-10-22T12:43:50.233z)

returns

InvalidCastException: Object must implement IConvertible. System.Convert.ChangeType(object value, Type conversionType, IFormatProvider provider) InvalidCastException: Failed to convert parameter value from a DateTimeOffset to a DateTime. System.Data.SqlClient.SqlParameter.CoerceValue(object value, MetaType destinationType, out bool coercedToDataFeed, out bool typeChanged, bool allowStreaming) TargetInvocationException: Exception has been thrown by the target of an invocation. System.RuntimeMethodHandle.InvokeMethod(object target, object[] arguments, Signature sig, bool constructor, bool wrapExceptions)

$filter=IsActive eq true and IsAutomated eq true and website ne null and length(website) gt 0
returns the results as I would expect (Interestingly enough, they are the exact same result set)

Controller:

[EnableQuery(PageSize = 20)]
public IQueryable<Location> Get()
{
    return _locationService.GetLocationsAsQueryable();
}
...
public IQueryable<Location> GetLocationsAsQueryable()

[erbaker]

I don't know if this is related or still an issue or not, but I'm seeing some strange behavior from parsing DateTime type in .net.

?$filter=IsActive eq true and IsAutomated eq true and website ne null and length(website) gt 0 and (LastUpdated eq null or date(LastUpdated) lt 2018-10-22T12:43:50.233z)

returns

InvalidCastException: Object must implement IConvertible. System.Convert.ChangeType(object value, Type conversionType, IFormatProvider provider) InvalidCastException: Failed to convert parameter value from a DateTimeOffset to a DateTime. System.Data.SqlClient.SqlParameter.CoerceValue(object value, MetaType destinationType, out bool coercedToDataFeed, out bool typeChanged, bool allowStreaming) TargetInvocationException: Exception has been thrown by the target of an invocation. System.RuntimeMethodHandle.InvokeMethod(object target, object[] arguments, Signature sig, bool constructor, bool wrapExceptions)

$filter=IsActive eq true and IsAutomated eq true and website ne null and length(website) gt 0
returns the results as I would expect (Interestingly enough, they are the exact same result set)

Controller:

Alright, so it looks like it was definitely me and DateTime is not supported, but DateTimeOffset is. I changed my db model and now it works without any issue. Hope it helps someone else.

[xuzhg]

@All I merged a change to make the functionality back compatible. If It works, would you please try the nightly and give us a feedback?

[ceouten]

@xuzhg I was able to test the nightly and it worked. What is the timeline on when this will be officially released? This change should allow us to upgrade.

The filter I used to test was "$filter=CreatedDateUtc gt 2018-12-25" where CreateDateUtc is a DateTimeOffset. I also tested with a nullable DateTimeOffset and that worked as well.

[freeranger]

@ceouten did you test it with a regular DateTime at all?
Thanks

[ceouten]

@freeranger I did not.

[ceouten]

@xuzhg I noticed that the ticket for this fix is on the 7.2 milestone. Is there a rough time frame on when that will be released?

[xuzhg]

So far no, we hope soon.

[ceouten]

Just wanted to check in and see if there was a planned release date for 7.2
Where is the best place to follow for this information? Right now I'm just casually checking nuget or http://odata.github.io/WebApi/