Parsing an OData filter string

241 views Asked by At

I'm using radzen to build a filter and apply it to a view that acts as my data set. Most everything works fine however when I try to filter by DateTime I get an error

Conversion failed when converting date and/or time from character string.

This is coming from the end resulting query which directly below. Which has the time portion which I don't need/can't use that way.

SELECT 
    [a].[SellBy]
    , [a].[ProductName]
FROM 
    [dbo].[Products] AS [a]
WHERE
    [a].[SellBy] = '2023-02-05T00:00:00.0000000'

In my code to convert the OData filter I have

    if (IsNullableType(left.Type) && !IsNullableType(right.Type))
    {
        if(right is ConstantExpression offset)
        {
            if(offset.Value is DateTimeOffset dateTimeOffset)
            {
                DateTime? dateTime = dateTimeOffset.DateTime;
                right = ConstantExpression.Constant(dateTime, typeof(DateTime?));
            }
        }
    }

Which gets me the below expression I pass directly to an entity framework where clause

(Products.SellBy == 9/6/2023 12:00:00 AM)

Then generates the problematic where clause

[a].[SellBy] = '2023-02-05T00:00:00.0000000'

OData Query

SellBy eq 2023-09-06T00:00:00.000Z

My question is how can I parse the above OData filter into a way that will allow me do run = <> < <= >= queries for dates. I'm close however my resulting query from the expression I'm building includes the time portion which I can't use.

There is more to the code including a section where I parse the OData query into a OData expression using an EDM which I'm not super familiar with. I left that out for brevity but I can post it if need be. I didn't include it because I think it's working fine. I'm able to run bool/text predicates using what I have so far.

0

There are 0 answers