Using a DbContext with OData and $expand results in InvalidOperationException

[Avrohom]

[ChrisJollyAU]

OData is trying to do a complex query which results in it needing to do something that is unsupported in in SQL capabplities understood by Jet.

In this case we likely are querying into a navigational property from a parent object. Furthermore we are wanting a specific row (possibly a first or last) after it is been ordered by one of its properties.

For the sub-expression after being ordered by one of its columns, we need a temporary numbering sequence linked to that result set (if you didn't order by its primary key the ordering wouldn't be in the same sequence). This is temporarily generated by the row number expression (ROW_NUMBER for SQL Server).

Unfortunately there is no direct SQL understood by Jet/MS Access to do this.

Best way to do it normally would likely be to simplify things and just query for the list of parent objects. After that you could loop through the list client side and for each one issue its own query to do the query on the navigational property. Then you are likely to have a simpler query something like: SELECT TOP 1 ... FROM ... WHERE ... ORDER BY ...

With you using OData you have much less visibility as to how the query is set up