DataGrid date column filter exception

Radzen.Blazor Components
1

Hello. I have a datagrid with a date column i would like to be able to do some filtering in. When i try to set the following:
image

I get an exception:
System.FormatException: String '2023-01-01T01.01.00.000Z' was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(ReadOnlySpan1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at System.DateTime.Parse(String s, IFormatProvider provider, DateTimeStyles styles) at Radzen.QueryableExtension.GetColumnFilter[T](RadzenDataGridColumn1 column, String value, Boolean second)
at Radzen.QueryableExtension.ToFilterString[T](IEnumerable1 columns) at Radzen.Blazor.RadzenDataGrid1.InvokeLoadData(Int32 start, Int32 top)
at Radzen.Blazor.RadzenDataGrid1.Reload() at Radzen.Blazor.RadzenDataGrid1.OnFilter(ChangeEventArgs args, RadzenDataGridColumn1 column, Boolean force, Boolean isFirst) at Radzen.Blazor.RadzenDataGrid1.ApplyFilter(RadzenDataGridColumn`1 column, Boolean closePopup)
at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

Does anyone have any idea? It seems to happen in the component, and not in my code.

2

I should mention it also happens on your demo site: Blazor DataGrid Simple filter mode (radzen.com)

3

image
image1920×1080 179 KB

image
image1920×1080 184 KB

4

image
image1872×975 172 KB

This is what i get on your website

5

I’m afraid that I don’t know why you get this error - you might need to clear your cache. If you believe you’ve found a bug feel free to submit pull request.

6

I don't know either.
I just downloaded a fresh chrome and firefox (I only had edge installed on this machine), and i keep getting the error.

7

I'm afraid "clear cache" is not the problem.

I face the exact same issue (currently using the latest "Radzen.Blazor" Version="4.13.5") in my code.

Also, same as Christian, I see the problem in your online demo: https://blazor.radzen.com/datagrid-simple-filter.

In your demo I can't know, but in my code, EF Core (using SQL Server) show the following in its DebugView Expression for the query:

.Call System.Linq.Queryable.Where(
    .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
    '(.Lambda #Lambda1<System.Func`2[ClientImport.Shared.ClientDataExtended,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[ClientImport.Shared.ClientDataExtended,System.Boolean]>(ClientImport.Shared.ClientDataExtended $var1)
{
    $var1.EntryDate == .Constant<System.DateTime>(6/14/2023 00:00:00)
}

This throws the Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string. exception when trying to apply the filter because of course it is a wrong date format for SQL Server.

The code (client and server) is running in my local machine, which is configured for English (United State) regional format.

It doesn't matter which FormatString you set in the grid's column. Setting "{0:yyyy-MM-dd}" or "{0:d}" behaves the same on the server side. It makes sense because I guess FormatString is only affecting how the data is displayed at the client side.

In summary, the question is, how can the grid be configured to send a date (or date-time) filter in the proper format to the server side?

Many thanks!

EDIT (more info):

The contents of args.Filter at the server side is EntryDate = DateTime("2023-06-14"), when I set FormatString="{0:yyyy-MM-dd}" at the client side (which is not how I want the dates to be displayed but I was trying to make it work somehow...

However, the EF query filter at the server side is "[Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression].Where(Param_0 => (Param_0.EntryDate == 6/14/2023 00:00:00))"

8

Solved!

See c# - How do I get EntityFrameworkCore generated SQL to use the right format for DateTime objects? - Stack Overflow

In summary, in the model class, I've added [Column(TypeName = "datetime")] decorating the DateTime column.

[Column(TypeName = "datetime")] public DateTime EntryDate { get; set; }
9

If the model was scaffolded from Radzen or Radzen Blazor Studio such attribute will be generated by default.

10

As said, I see the problem in your online demo: https://blazor.radzen.com/datagrid-simple-filter. Is it using Radzen or Radzen Blazor Studio?

11

No, our component demos are not scaffolded using Radzen or Radzen Blazor Studio.