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 throwsA 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?