I am using a DateTime column with filterable options enabled. When I apply a filter on this column, the request.Filters has a valid DateTime but it is applying an incompatible format when it calls SQL Server. Here is my code:
public async Task<JsonResult> OnGetDataAsync([DataSourceRequest]DataSourceRequest request, CustomFilterModel customFilters)
{
IQueryable<DAL.Product> products = this.productService.GetByCriteria(customFilters);
JsonSerializerSettings jsonSettings = new JsonSerializerSettings()
{
ContractResolver = new DefaultContractResolver()
};
return new JsonResult(await products.ToDataSourceResultAsync(request), jsonSettings);
}
This is what EF generates: (abbreviated for clarity)
SELECT ....
WHERE [p].[DateAdded] <= '2018-10-01T00:00:00.0000000'
**Error:** System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
Basically, the generated SQL query does not work when I run it directly in SMMS. It only works if I convert the date to **'2018-10-01T00:00:00'**
Tech stack: .NET Core 2.2, Razor Pages, Kendo 2018.3.911, SQL 2018
public async Task<JsonResult> OnGetDataAsync([DataSourceRequest]DataSourceRequest request, CustomFilterModel customFilters)
{
IQueryable<DAL.Product> products = this.productService.GetByCriteria(customFilters);
JsonSerializerSettings jsonSettings = new JsonSerializerSettings()
{
ContractResolver = new DefaultContractResolver()
};
return new JsonResult(await products.ToDataSourceResultAsync(request), jsonSettings);
}
This is what EF generates: (abbreviated for clarity)
SELECT ....
WHERE [p].[DateAdded] <= '2018-10-01T00:00:00.0000000'
**Error:** System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
Basically, the generated SQL query does not work when I run it directly in SMMS. It only works if I convert the date to **'2018-10-01T00:00:00'**
Tech stack: .NET Core 2.2, Razor Pages, Kendo 2018.3.911, SQL 2018