CODEWITHSUNDEEP
asp.netentity-framework-6odata
Mike Feltman
Mike Feltman

Reputation: 5176

Calculate max date with oData V4

I am trying to get the date that records with various status codes were last created. I am issuing this query:

http://localhost/FTPOrdersoDataService/odata/FTP_ORDER_STATUS_LOG?$apply=groupby((STATUS), aggregate(FTP_ORDERS_ID with max as lastCreated))

The following error is returned": 

{
"error": {
"code": "",
"message": "An error has occurred.",
"innererror": {
"message": "Argument types do not match",
"type": "System.ArgumentException",
"stacktrace": "   at System.Linq.Expressions.Expression.Bind(MemberInfo member, Expression expression)\r\n   at System.Web.OData.Query.Expressions.AggregationBinder.BindSelect(IQueryable grouping)\r\n   at System.Web.OData.Query.ApplyQueryOption.ApplyTo(IQueryable query, ODataQuerySettings querySettings)\r\n   at System.Web.OData.Query.ODataQueryOptions.ApplyTo(IQueryable query, ODataQuerySettings querySettings)\r\n   at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor, ODataQueryContext queryContext)\r\n   at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"
}
}
}

If I change the request to: 

http://localhost/FTPOrdersoDataService/odata/FTP_ORDER_STATUS_LOG?$apply=groupby((STATUS), aggregate(FTP_ORDERS_ID with max as lastCreated))

then it functions properly, so obviously the issue is with the CREATEDDATE field. CREATEDDATE is defined as TIMESTAMP(6) in Oracle and is defined as DateTime in my EF model.

Is there a secret for making max (and other aggregation functions) work with dates?

Upvotes: 3

Views: 2260

Answers (1)

Chris Schaller
Chris Schaller

Reputation: 16594

Tested in Microsoft.AspNet.OData.7.4.1

In the past there were issues with this, however the OData implementation has evolved, you can now use the following query syntax to cast the field to a type that is supported by the standard EnableQueryAttribute:

aggregate(cast(CREATEDDATE, Edm.DateTimeOffset) with max as lastCreated)

http://localhost/FTPOrdersoDataService/odata/FTP_ORDER_STATUS_LOG?$apply=groupby((STATUS), aggregate(cast(CREATEDDATE, Edm.DateTimeOffset) with max as lastCreated))

Upvotes: 1

Related Questions