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:

• https://github.com/OData/AspNetCoreOData/issues/545
• https://github.com/OData/AspNetCoreOData/issues/989
• https://github.com/OData/AspNetCoreOData/issues/1148
• https://github.com/OData/AspNetCoreOData/pull/450
• https://github.com/OData/AspNetCoreOData/pull/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/

[JohnYoungers]

I finally got around to implementing this, and I'll post the code below in case anyone else requires similar functionality. A lot of the existing logic is internal, so this required some reflection/duplication:

public class MyFilterBinder : FilterBinder
{
	private static readonly Type s_odataDateType = typeof(Microsoft.OData.Edm.Date);

	private static readonly Dictionary<BinaryOperatorKind, ExpressionType> s_dateOperators = new()
	{
		{ BinaryOperatorKind.Equal, ExpressionType.Equal },
		{ BinaryOperatorKind.GreaterThan, ExpressionType.GreaterThan },
		{ BinaryOperatorKind.GreaterThanOrEqual, ExpressionType.GreaterThanOrEqual },
		{ BinaryOperatorKind.LessThan, ExpressionType.LessThan },
		{ BinaryOperatorKind.LessThanOrEqual, ExpressionType.LessThanOrEqual },
		{ BinaryOperatorKind.NotEqual, ExpressionType.NotEqual },
	};

	public override Expression BindBinaryOperatorNode(BinaryOperatorNode binaryOperatorNode, QueryBinderContext context)
	{
		if (s_dateOperators.TryGetValue(binaryOperatorNode.OperatorKind, out var binaryOperatorType))
		{
			var left = Bind(binaryOperatorNode.Left, context);
			var right = Bind(binaryOperatorNode.Right, context);

			if (left.Type == s_odataDateType || right.Type == s_odataDateType)
			{
				var targetType = left.Type == s_odataDateType ? right.Type : left.Type;

				return Expression.MakeBinary(
					binaryOperatorType,
					ExtractDateConstant(left, targetType),
					ExtractDateConstant(right, targetType),
					false,
					method: null
				);
			}
		}

		return base.BindBinaryOperatorNode(binaryOperatorNode, context);
	}

	private static Expression ExtractDateConstant(Expression expression, Type targetType)
	{
		if (expression is not MemberExpression { Expression: ConstantExpression constant })
		{
			return expression;
		}

		var val = constant.Value;
		// LinqParameterContainer is internal, so we'll use reflection to get the Property value.
		if (val?.GetType().GetProperty("Property") is not { } pi || pi.GetValue(val) is not Microsoft.OData.Edm.Date date)
		{
			return expression;
		}

		object? targetValue = null;
		var underlyingTargetType = Nullable.GetUnderlyingType(targetType) ?? targetType;
		if (underlyingTargetType == typeof(DateTime))
		{
			targetValue = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0, DateTimeKind.Utc);
		}
		else if (underlyingTargetType == typeof(DateTimeOffset))
		{
			targetValue = new DateTimeOffset(date.Year, date.Month, date.Day, 0, 0, 0, TimeSpan.Zero);
		}
		else if (underlyingTargetType == typeof(DateOnly))
		{
			targetValue = new DateOnly(date.Year, date.Month, date.Day);
		}

		return targetValue is not null ? Expression.Constant(targetValue, targetType) : expression;
	}
}