2

I have asp.net core app and I'm displaying data in a view with devextreme datagrid, that calls odata endpoint to get data for the grid. I have a model for a client and I would like to filter clients based on date of birth. This is my model containing BirthDate of type DateTime.

public class GridClientDTO : GridDTO
    {
        ...
        public DateTime BirthDate { set; get; }
        ...
    }

My definition of devextreme data grid:

@(Html.DevExtreme().DataGrid().ID("clientsGrid").HeaderFilter(o => o.Visible(true))
                    .CustomizeColumns("customizeColumns")
                .Columns(columns =>
                {
...
columns.Add().DataField("BirthDate").AllowHeaderFiltering(false).DataType(GridColumnDataType.Date).Format("dd.MM.yyyy").CssClass("col-sm-3 col-xs-3 text-muted").HeaderCellTemplate(@Html.Raw("<b>Birth Date</b>").ToHtmlString());
                })

                .DataSource(ds => ds
                    .OData()
                    .Version(4)
                    .Url(@Url.Content("~/odata/OClients"))
                    .JSONP(false)
                    .Key("Id")
                )
                        .RowAlternationEnabled(true)
                    .AllowColumnResizing(true)
                    .ShowRowLines(true)
                    .ShowColumnLines(true)
                    .HoverStateEnabled(true)
                    .Paging(page => page
                            .PageSize(25)
                            )
                                    .Sorting(sorting => sorting.Mode(GridSortingMode.Multiple))
                    .FilterRow(f => f
                            .Visible(true)
                            .ApplyFilter(GridApplyFilterMode.Auto)

                            )
                    .Export(e => e.Enabled(false)
                            .AllowExportSelectedData(true)
                            )
                    .Selection(s => s
                         .Mode(0)
                         .ShowCheckBoxesMode(0)
                        )
                    .Pager(p => p
                        .Visible(true)
                        .ShowPageSizeSelector(true)
                        .AllowedPageSizes(new int[] { 10, 25, 50, 100 })
                        .ShowInfo(true)
                        .ShowNavigationButtons(true)
                        )
)

I would like to filter all clients that were born on 2022-08-15. I inspect ODataQueryOptions parameter in my OData method and this is raw value of my filter: "(BirthDate ge 2022-08-15T00:00:00Z) and (BirthDate lt 2022-08-16T00:00:00Z)"

But when doing actual DB query, the localization changes the query to (I'm in +1 timezone and using daylight saving time):

SELECT ... g."BirthDate", ...
FROM public."GridClients" AS g
WHERE (g."BirthDate" >= '2022-08-15 02:00:00') AND (g."BirthDate" < '2022-08-16 02:00:00')
LIMIT 25

Birth dates are stored with time set to 00:00:00 and therefore the query returns clients with birth date a day later than intended. Ideas I have tried:

  • I tried changing BirthDate to type DateOnly. Filter looks like this: "(ChargeDate ge '2022-08-16') and (ChargeDate lt '2022-08-17')" OData throws A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'GreaterThanOrEqual'.'
  • I tried setting timezone to UTC on registering ODate service, this seems to have no effect at all
services.AddControllersWithViews().AddOData(opt => {
                opt.Select().Expand().Count().SetMaxTop(null).Filter().OrderBy().SkipToken()
                .AddRouteComponents("odata", GetEdmModel());
                opt.TimeZone = TimeZoneInfo.Utc;
});

Is it possible to somehow modify behaviour of either DataGrid or OData to get my desired result?

0 Answers0