Cast Edm.String to Edm.Int32 in a lambda expresion in a $filter

[cam-m]

I have the following entities (highly simplified).

EntityA

• EntityBList: Collection(EntityB)

EntityB

• Value: Edm.String

The Value property of EntityB can store values that can be cast to numbers, but also values that are arbitrary strings.

I want to filter Entity 1 based on these values using a Lambda expression like this:

EntityAs?$filter=EntityB/any(link:(cast(link/Value, Edm.Int32) gt 5))

This works fine
EntityAs?$filter=EntityB/any(link:(cast(link/Value, Edm.String) eq '5'))

and generates SQL like this

[Extent2].[Value] = @p__linq__0)
    ))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'5'

This
EntityAs?$filter=EntityB/any(link:(cast(link/Value, Edm.String) gt '5'))

is not going to work for a numeric comparison (E.g. The expression '100' gt '5' returns false when compared as strings)

Casting the value to an Int.32 like this:

EntityAs?$filter=EntityB/any(link:(cast(link/Value, Edm.Int32) eq 5))

...results in the lambda filter expression being removed from the EF6 generated SQL, and replaced with this:

 EXISTS (SELECT 
        1 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        WHERE 1 = 0  

Is there anything I can do to make this work?

Assemblies affected

Microsoft.AspNet.OData 7.6.3
Microsoft.OData.Core 7.14.0
Microsoft.OData.Edm 7.14.0

Reproduce steps

See above

Expected result

Should be able to cast a string to an int (according to OData v4 specs

Actual result

Cast string to an int doesn't work.

Additional detail

N/A

[chrisspre]

I assume EntityB/Value is declared as type string in the OData Model, Entity Framework and SQL.
This is does allow to translate the comparison to the constant string '5' as seen in @p__linq__0=N'5'

But strings cannot be cast (i.e. type conversion) to a number. One would need to parse the string into a number.
I can imagine that a custom function can be used in EF and OData to do this.
But the more manageble way would be to clean up the source data and have an additional integer property for the EntityB's that have a number in the Value property.
Is that possible to do? (for example using a SQL update query that uses SQLs PARSE statement to add the additional column)

[cam-m]

Yes the property is declared as a string throughout the app and is used to hold string representations of various types of values ( dates, numbers, strings)

The OData lists support for casting an Edm.String to any Primitive type provided the string contains a valid representation of the targeted Primitive.

Excerpt from section 5.1.1.10 of the OData Version 4.01 Part 2 URL Conventions

The cast function follows these assignment rules:
1-4. omitted
5. Edm.String, or a type definition based on Edm.String, can be cast to a primitive type if the
string contains a literal representation for the target type.
6-11 omitted

The cast function is optional for primitive values (first five rules) and up-casts (seventh rule).
If the cast fails, the cast function returns null

And AFAIK a 'string' can be cast to an INT in SQL Server. E.g. select cast('1234' as INT) as result - am I missing something?

So if the answer is that point 5 of OData's cast function is not supported for whatever reason thats cool, but the behaviour I'm seeing is less than ideal. At the very least if its unsupported, an error message to the effect should be thrown. As it is you get a http 200 success with no results because of the expression WHERE 1 = 0 .

Yes, you're spot on, we could migrate our DB structure, or, more attractive, create a View with values cast or converted to their respective types. However the effort to do this can be avoided if this lib supports the OData spec in respect to cast.

[chrisspre]

I apologize, I made too many assumptions based on other languages. Thanks a lot for the link to the spec. I consider this a bug given that information now and we triage again.