When I pass date filter to Odata, its generating query as follows: causing poor performance
SELECT TOP(10000) [$it].[ID], Column1, ....
FROM tablename AS [$it]
WHERE (((DATEPART(year, [$it].[COBDate]) * 10000) + (DATEPART(month, [$it].[COBDate]) * 100)) + DATEPART(day, [$it].[COBDate])) = (((2020 * 10000) + (12 * 100)) + 4)
ORDER BY [$it].[ID]
My question is: How to generate query as below for better performance?
SELECT TOP(10000) [$it].[ID], Column1, ....
FROM tablename AS [$it]
WHERE COBDate = '2020-12-04'
ORDER BY [$it].[ID]
Technical Details: [enter image description here][1] [1]: https://i.stack.imgur.com/t4lbY.png
Odata Request:
https://localhost:5001/odata/Product?api-version=1.0&%24filter=COBDate%20Eq%202020-12-04
Model class:
public DateTime COBDate { get; set; }
Model Configuration:
product.Property(x => x.COBDate).AsDate();
Controller:
[Produces("application/json")]
[ProducesResponseType(typeof(ODataValue<IEnumerable<Product>>), Status200OK)]
[ODataRoute("Product")]
[ConfigurableEnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IActionResult Get()
{
try
{
return Ok(context.tablename.AsQueryable());
}
catch (Exception ex)
{
logger.LogError(ex, "Call failed.");
return BadRequest($"{ex}");
}
}
