C#•15mo ago

❔ Parsing an OData filter string

I have a project that has a mildly complex query requirement. 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'
I'm very new to using expressions directly but 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?));
                }
            }
        }

SELECT 
    [a].[SellBy]
    , [a].[ProductName]
FROM 
    [dbo].[Products] AS [a]
WHERE
    [a].[SellBy] = '2023-02-05T00:00:00.0000000'
I'm very new to using expressions directly but 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 which

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

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

Then generates the problematic where clause

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

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

OData Query

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

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

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.

2 Replies

Omnissiah•15mo ago

is this coming in a serialized way? don't you have to call DateTime.Parse?

Accord•15mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Parsing an OData filter string