Datetime filter parameter odata problem

Carlos_Carminati · July 4, 2019, 2:35pm

Hi,
I'm trying to filter a result set based on a datetime

We use TIME_STAMP eq 2019-05-29T19:15:34.51Z as example. Error is present when controller method try to serialize the response:

dotnet: info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:5000/odata/KPIs/KpiProyHs?$filter=TIME_STAMP%20eq%202019-05-29T19%3A15%3A34.51Z

dotnet: info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Route matched with {action = "GetKpiProyHs", controller = "KpiProyHs"}. Executing action KpIs.Controllers.KpIs.KpiProyHsController.GetKpiProyHs (server)
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method KpIs.Controllers.KpIs.KpiProyHsController.GetKpiProyHs (server) - Validation state: Valid
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Executed action method KpIs.Controllers.KpIs.KpiProyHsController.GetKpiProyHs (server), returned result Microsoft.AspNetCore.Mvc.ObjectResult in 0.009ms.

dotnet: info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.3-servicing-35854 initialized 'KpIsContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor[1]
Executing ObjectResult, writing value of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[[KpIs.Models.KpIs.KpiProyH, server, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'.

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [$it].[EQUIP_NO], [$it].[COMP_CODE], [$it].[COMP_MOD], [$it].[MAINT_SCH_TASK], [$it].[TIME_STAMP], [$it].[TIME_STAMP_UPDATE], [$it].[KPI_NFD], [$it].[KPI_NSD]
FROM [dbo].[KPI_Proy_H] AS [$it]
WHERE [$it].[TIME_STAMP] = '2019-05-29T19:15:34.5100000'
fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'KpIs.Data.KpIsContext'.
System.Data.SqlClient.SqlException (0x80131904): Error al convertir una cadena de caracteres en fecha y/u hora.

What is wrong?

Thanks

enchev · July 4, 2019, 4:14pm

Hi,

What’s the type of the column? According to the exception the sql server cannot parse the string as date time.

Carlos_Carminati · July 4, 2019, 8:43pm

Is a DateTime no Nullable:

I wrote a server custom method to test another approach and get a different error.

My method:

public IActionResult GetProyHsFromP(string EQUIP_NO, string COMP_CODE, string COMP_MOD, string MAINT_SCH_TASK, DateTime TIME_STAMP)
{
var historial = context.KpiProyHs
.Where(x => x.EQUIP_NO == EQUIP_NO && x.COMP_CODE == COMP_CODE && x.COMP_MOD == COMP_MOD && x.MAINT_SCH_TASK == MAINT_SCH_TASK && x.TIME_STAMP == TIME_STAMP)
.OrderByDescending(x => x.TIME_STAMP_UPDATE)
.ToList();

        return Json(historial, new JsonSerializerSettings()
        {
            ContractResolver = new DefaultContractResolver()
        });
    }

The error:

enchev · July 4, 2019, 9:57pm

You get this when you try to filter the column in the DataGrid? I’ll try to replicate your case locally to see if I can reproduce the same error.

Carlos_Carminati · July 4, 2019, 10:02pm

I'm trying to show second level items in a datagrid using a template.

I have a master record showed and I want to expand row and show history records that are in another table.

This is my RowExpand:

enchev · July 5, 2019, 7:33am

Hi Carlos,

Please change the TIME_STAMP parameter to string and use toISOString method so the Date is converted to a string. On the server you can use
DateTime.Parse(TIME_STAMP, null, System.Globalization.DateTimeStyles.RoundtripKind)

korchev · July 5, 2019, 8:36am

@Carlos_Carminati I think I know why the custom method fails with a DateTime parameter. The value you are passing is a string and our code doesn't currently handle this. Setting the TIME_STAMP parameter to ${new Date(event.TIME_STAMP)} should work. We will try to handle that in Radzen.

Carlos_Carminati · July 7, 2019, 6:51pm

Thank you for your recommendations. Finally I used a store proc to make the select with same five parameters used in custom method and then works fine.

CREATE PROCEDURE GetHistorialByProyeccion
@EQUIP_NO nchar(10),
@COMP_CODE nchar(10),
@COMP_MOD nchar(10),
@MAINT_SCH_TASK nchar(10),
@TIME_STAMP datetime

AS
BEGIN

SET NOCOUNT ON;

SELECT [EQUIP_NO]
,[COMP_CODE]
,[COMP_MOD]
,[MAINT_SCH_TASK]
,[TIME_STAMP]
,[TIME_STAMP_UPDATE]
,[KPI_NSD]
,[KPI_NFD]
FROM [dbo].[KPI_Proy_H]
WHERE EQUIP_NO = @EQUIP_NO
AND COMP_CODE = @COMP_CODE
AND COMP_MOD = @COMP_MOD
AND MAINT_SCH_TASK = @MAINT_SCH_TASK
AND TIME_STAMP = @TIME_STAMP

END
GO

In first case, when I tried to use DateTime parameter directly in oData filter fail due entity framework pass this value as string with an incorrect format:

SELECT [$it].[EQUIP_NO], [$it].[COMP_CODE], [$it].[COMP_MOD], [$it].[MAINT_SCH_TASK], [$it].[TIME_STAMP], [$it].[TIME_STAMP_UPDATE], [$it].[KPI_NFD], [$it].[KPI_NSD]

FROM [dbo].[KPI_Proy_H] AS [$it]

WHERE [$it].[TIME_STAMP] = '2019-05-29T19:15:34.5100000'

Reading about using datetime filter in oData there are different ways in v3 and v4 but doesn't work in my case. This is a Radzen issue or EF issue?

Thanks

korchev · July 8, 2019, 6:12am

Date filtering seems to work OK with the Northwind database. Here is a test query: https://services.radzen.com/odata/Northwind/NorthwindOrders?$filter=OrderDate%20ge%202006-07-04T00:00:00.000Z&$top=10&$skip=10&$count=true

The only difference is that the date ends with a 'Z'.

This is one of the date formats which OData 4 supports. Can you paste the URL of a query that fails in the described manner? You can also provide the DB schema of that table so we can test locally.

jamesmd2 · July 12, 2019, 8:48pm

Hi @korchev

I've encountered similar issue with using grid date filtering.

Use Grid built-in column filter and select a date
At this point the grid stays permanently in the “loading” state

Here’s what I found:

The generated odata
GET /odata/LOBProductionData/DailyNotes?$filter=Date%20eq%202014-11-10T00:00:00.000Z&$top=10&$skip=0&$count=true HTTP/1.1
The subsequent SQL query that was generated (sql server profiler)
SELECT COUNT_BIG(*)
FROM [dbo].[DailyNotes] AS [$it]
WHERE [$it].[Date] = '2014-11-10T00:00:00.0000000'
My date column is a ‘datetime’ column and from what I can determine datetime only supports 3 digits of precision for the millisecond – the generated SQL has 7
If I use the generated query and remove the last 4 0’s from the milliseconds, the query works.

Any ideas ? I see your Northwind odata query has the same date format as mine, but yet it works.

SCHEMA

CREATE TABLE [dbo].[DailyNotes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL,
[Site] varchar NOT NULL,
[Notes] varchar NULL,
CONSTRAINT [PK_DailyNotes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

APPLICATION TRACE INFO:

dotnet: info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:5000/odata/LOBProductionData/DailyNotes?$filter=Date%20eq%202014-11-10T00:00:00.000Z&$top=10&$skip=0&$count=true
info: Microsoft.AspNetCore.Cors.Infrastructure.CorsService[4]
CORS policy execution successful.

dotnet: info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Route matched with {action = "GetDailyNotes", controller = "DailyNotes"}. Executing action LobWeb.Controllers.LobProductionData.DailyNotesController.GetDailyNotes (server)

dotnet: info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method LobWeb.Controllers.LobProductionData.DailyNotesController.GetDailyNotes (server) - Validation state: Valid
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Executed action method LobWeb.Controllers.LobProductionData.DailyNotesController.GetDailyNotes (server), returned result Microsoft.AspNetCore.Mvc.ObjectResult in 0.0072ms.

dotnet: info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.3-servicing-35854 initialized 'LobProductionDataContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None

dotnet: info: Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor[1]
Executing ObjectResult, writing value of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[[LobWeb.Models.LobProductionData.DailyNote, server, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'.

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT_BIG(*)
FROM [dbo].[DailyNotes] AS [$it]
WHERE [$it].[Date] = '2014-11-10T00:00:00.0000000'

dotnet: fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'LobWeb.Data.LobProductionDataContext'.
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataRead

korchev · July 13, 2019, 7:17am

This sounds like the issue described here. We will investigate and report our findings.

jamesmd2 · July 13, 2019, 2:17pm

Thanks. That looks to be it.

jamesmd2 · July 13, 2019, 2:27pm

As a test I modified my table model code and added the [Column(TypeName="datetime")] attribute to the date column. Worked like a charm - SQL code generated correctly.
Of course I'd prefer not to have to modify all my generated model code

enchev · July 14, 2019, 7:14am

You can use OnModelBuilding partial method of the DbContext to specify that your date-time properties are datetime instead datetime2 (default for EF):

    public partial class NorthwindContext
    {
        partial void OnModelBuilding(ModelBuilder builder)
        {
           builder.Entity<Northwind.Models.Northwind.Order>()
                  .Property(p => p.OrderDate)
                  .HasColumnType("datetime");
        }
    }

enchev · July 15, 2019, 5:41am

Hi @jamesmd2,

We will fix this in Radzen code-generation, you will need to reinfer your data-source.

enchev · July 16, 2019, 4:35am

Fixed in Radzen 1.19.4. You need to reinfer the data source.