Dates in $filter parsed as DateTime with DateTimeKind.Unspecified

[jamerst]

Npgsql 6.0 has a breaking change around timestamp mapping which breaks querying via OData. In order to query a Postgres column of type 'timestamp with time zone', the corresponding DateTime needs to have Kind = DateTimeKind.Utc.

However, all dates in the $filter clause are parsed with DateTimeKind.Unspecified, which causes Npgsql to throw an exception and results in OData returning an invalid and incomplete JSON response.

Is there a way to override the parsing of dates? Would it be possible for setting ODataOptions.TimeZoneInfo = TimeZoneInfo.Utc to also set the Kind of any parsed dates to UTC?

[xuzhg]

@jamerst We are working on a refactor for FilterBinder to allow customers to override the binding process.

Now, the setting on ODataOptions could help you resolve the problem.

[jamerst]

Thanks for the reply @xuzhg

How does the TimeZoneInfo option help? Dates are still being parsed as unspecified when it is set to TimeZoneInfo.Utc. Or are you referring to a different property of ODataOptions?

[MrKevHunter]

I agree we are seeing the same

#384

[MrKevHunter]

Thanks for the reply @xuzhg

How does the TimeZoneInfo option help? Dates are still being parsed as unspecified when it is set to TimeZoneInfo.Utc. Or are you referring to a different property of ODataOptions?

fixed in today's nightly build!

[jamerst]

@xuzhg

This issue still doesn't appear to be resolved as of version 8.0.10.

Apologies for the long period of inactivity on the issue, I assumed it was fixed but foolishly didn't confirm it, so I've only just encountered it again.

The issue with dates in the query is still present, but I've also discovered very similar issue with POST, PUT and PATCH endpoints where any DateTime properties in the entity are created with DateTimeKind.Unspecified.

I've created a basic API to show the issue: https://github.com/jamerst/ODataDateDemo.

This can only demonstrate the issue with POST, the issue with parsing dates in $filter is harder to demonstrate without a database to connect to since it's not a problem when querying in-memory.

To reproduce the issue, send a POST request to https://localhost:7278/TestEntity with a JSON body of

{
    "Id": 1,
    "Date": "2022-01-02T12:00:00Z"
}

The endpoint will simply return the Kind of the Date property. It should be Utc, but it returns Unspecified for me.

I've tried many different combinations of the following in order to fix this, but nothing has worked:

• Using the [FromBody] attribute on the entity parameter (causes the observed behaviour)
• Using the [FromODataBody] attribute on the entity parameter (breaks the endpoint, throws the following exception on request)

System.Runtime.Serialization.SerializationException: The last segment of the request URI 'TestEntity' was not recognized as an OData action.
    at Microsoft.AspNetCore.OData.Formatter.Deserialization.ODataActionPayloadDeserializer.GetAction(ODataDeserializerContext readContext)
    at Microsoft.AspNetCore.OData.Formatter.Deserialization.ODataActionPayloadDeserializer.ReadAsync(ODataMessageReader messageReader, Type type, ODataDeserializerContext readContext)
    at Microsoft.AspNetCore.OData.Formatter.ODataBodyModelBinder.ReadODataBodyAsync(ModelBindingContext bindingContext)
    at Microsoft.AspNetCore.OData.Formatter.ODataBodyModelBinder.BindModelAsync(ModelBindingContext bindingContext)
    at Microsoft.AspNetCore.Mvc.ModelBinding.Binders.BinderTypeModelBinder.BindModelAsync(ModelBindingContext bindingContext)
    at Microsoft.AspNetCore.Mvc.ModelBinding.ParameterBinder.BindModelAsync(ActionContext actionContext, IModelBinder modelBinder, IValueProvider valueProvider, ParameterDescriptor parameter, ModelMetadata metadata, Object value, Object container)
    at Microsoft.AspNetCore.Mvc.Controllers.ControllerBinderDelegateProvider.<>c__DisplayClass0_0.<<CreateBinderDelegate>g__Bind|0>d.MoveNext()
 --- End of stack trace from previous location ---
    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

• Using no attribute on the entity parameter (JSON body is just ignored and entity is null)
• Adding the [EnableQuery] attribute to the Post endpoint (has no effect)
• Setting the TimeZone property of ODataOptions to any other TimeZoneInfo (has no effect, does affect the response to the Get endpoint however)

[julealgon]

@jamerst this is not an answer to your original question, but have you considered migrating towards DateTimeOffset as an option?

[jamerst]

@julealgon good suggestion, should be a good workaround and probably fits my use case better anyway, I'll have to look into it further when I get chance.

[manikandantk88]

Hi,

The issue still persists in 8.0.11. Though I set the TimeZone property of ODataOptions, I receive DateTime Kind as Unspecified. Is there any workaround or fix without changing the Database column type?

[veselinbg]

Hi, is there any progress with this issue ? I tried the last version and the issue still exist.

[darkeagle76]

Hello,
I think the problem is related to the conversion from DateTimeOffet to DateTime, see the method DateTimeOffsetToDateTime in the class ExpressionBinderHelper.
It calls the method EdmPrimitiveHelper.ConvertPrimitiveValue to convert from DateTimeOffset to Datetime and in this method (row 102) there is a " return dateTimeOffsetValue.DateTime;":

else if (type == typeof(DateTime))
{
    if (value is DateTimeOffset)
    {
        DateTimeOffset dateTimeOffsetValue = (DateTimeOffset)value;
        TimeZoneInfo timeZone = timeZoneInfo ?? TimeZoneInfo.Local;
        dateTimeOffsetValue = TimeZoneInfo.ConvertTime(dateTimeOffsetValue, timeZone);
        return dateTimeOffsetValue.DateTime; //<----------------- This is the instruction I'm refering
    }

    if (value is Date)
    {
        Date dt = (Date)value;
        return (DateTime)dt;
    }

    throw new ValidationException(Error.Format(SRResources.PropertyMustBeDateTimeOffsetOrDate));
}

The instruction " return dateTimeOffsetValue.DateTime;", according to the microsoft documentation to DataTimeOffset.Value always return a Kind=Unspecified.
In my opinion the fix should be changing the above instruction to return DateTimeOffset.UtcDateTime that guarantee congruency between the DateTimeOffset (that intrinsecally have an offset) and DateTime (that do not have an offset) and always allow getting a DateTime value that is expressed in UTC.

The point is that it is not possible to perform this customization using dependency injection.
Someone know how to keep the attention of the odata developers on this point?

Thanks
D