Configuring CreateDateBinaryExpression Behavior

[JohnYoungers]

I believe when we encounter a filter like this: myDate le 2025-05-02

The expression per operand will be generated here, which creates a numeric representation:
https://github.com/OData/AspNetCoreOData/blob/main/src/Microsoft.AspNetCore.OData/Query/Expressions/ExpressionBinderHelper.cs#L425-L441

When combined with EFCore, we end up with SQL like so:

WHERE DATEPART(year, [i].[MyDate]) * 10000
	+ DATEPART(month, [i].[MyDate]) * 100
	+ DATEPART(day, [i].[MyDate]) <= 20250502

I couldn't find an explanation in the code, but is there any reason that couldn't have been a direct comparison (e.g. [i].[MyDate] <= '2025-05-02') in the expression?

Would it make sense to toggle such behavior? I don't believe there's an easy way to adjust it on the consuming end

[WanjohiSammy]

Hi @JohnYoungers, thank you for bringing up this issue.

When encountering a filter like myDate le 2025-05-02, the current implementation converts the date into a numeric representation. This behavior is implemented in the following code:
https://github.com/OData/AspNetCoreOData/blob/main/src/Microsoft.AspNetCore.OData/Query/Expressions/ExpressionBinderHelper.cs#L425-L441

I believe the reason behind this logic is to be able to apply the logical operations on date, such as eq, le, gt, ne, ge, lt, etc. This approach ensures that the Date type can be effectively used in filter expressions.

What issue are you facing with this conversion? Are you experiencing any problems? Could you please provide more details about the specific issue you're encountering?

[JohnYoungers]

No issues from a conversion standpoint, I'm just trying to understand in what scenarios it's needed, and if it's not needed, how I could potentially override it. From a performance standpoint the functionality is detrimental in that indexes wouldn't be utilized efficiently.

At least from a MSSQL standpoint, I'm not sure under which scenarios I'd want this behavior, opposed to comparing the values directly

[habbes]

@JohnYoungers thanks for bringing this to our attention. @xuzhg clarified that this was implemented as a workaround to support comparisons against date-only types. At the time there was the standard DateOnly type did not exist in .NET yet, so we used a custom Date type defined in the OData library. There's ongoing and pending work to replace OData's Date and TimeOfDay types with DateOnly and TimeOnly. Replacing this logic with native date comparisons should feature as part of that work.

Possibly related to:

• DateOnly quering not working #545
• The query specified in the URI is not valid. The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable1[System.DateOnly]' and 'System.Nullable1[System.DateTimeOffset]'.  #989
• [Question] Does OData $orderby support working with Edm.Date or DateOnly fields? #1148
• Support DateOnly and TimeOnly #450
• Fix: Add DateOnly and TimeOnly as Non-standard to standard EDM Primitive Conversions #1353

Regarding how to override it, perhaps you may able to do that with a custom IFilterBinder. You can implement an IFilterBinder that looks for date-related comparisons and customize the Expression generated from them. Here's an example of how to create a custom FilterBinder: https://devblogs.microsoft.com/odata/customizing-filter-for-spatial-data-in-asp-net-core-odata-8/