Open AliAlmutawakel opened 3 years ago
@AliAlmutawakel It's probably a problem of EF rather than ODataWebApi. Upgrade to ef core 5 and use AsSplitQuery() may help.
@AliAlmutawakel It's probably a problem of EF rather than ODataWebApi. Update to ef 5 and use AsSplitQuery() may help.
The EF query is generated by ODataWebApi.
I'm returning IQueryable instance inside a [EnableQuery] method. Expanding navigational properties happen in ODataWebApi code.
[HttpGet]
[EnableQuery(PageSize = 20)]
[ODataRoute("Customers({keyCustomerId})/BagItems")]
public IQueryable<BagItem> GetBagItems([FromODataUri] string keyCustomerId)
=> _db.BagItems.Where(bagItem => bagItem.CustomerId.Equals(keyCustomerId));
Is there another way ODataWebApi expand navigational properties without generating an unsupported SQL command?
@AliAlmutawakel I'm not sure. But I think the answer is no.
The sql query is not created by odata webapi, it is generated by ef. OData webapi only generate expression tree. Then ef use the expression tree to generate sql query. In fact, OData webapi does not care what kind of data source and orm tool you use.
Do you have a try with ef core 5 and AsSplitQuery()? Or you can downgrade to ef core 2. x if you don't want to upgrade.
Is there another way ODataWebApi expand navigational properties without generating an unsupported SQL command?
OData webapi only generate expression tree
Is there a possibility that OData WebAPI generates an expression tree that doesn't compile down to an 'outer apply' command? Read this comment for reference: https://github.com/dotnet/efcore/issues/19178#issue-533485460 where the LINQ expression creates a Join command instead of outer apply.
@AliAlmutawakel I'm not sure. But I think the answer is no.
The sql query is not created by odata webapi, it is generated by ef. OData webapi only generate expression tree. Then ef use the expression tree to generate sql query. In fact, OData webapi does not care what kind of data source and orm tool you use.
Do you have a try with ef core 5 and AsSplitQuery()? Or you can downgrade to ef core 2. x if you don't want to upgrade.
I get a similar issue. I try ef core 5 with AsSplitQuery() still get the exception Translating this query requires APPLY operation in SQL which is not supported on SQLite.
, but the same code works on ef core 2.x.
@qazq @AliAlmutawakel I'm sorry to mislead you to use AsSplitQuery() before try it myself. It can not work well with OData WebApi. I thought it could but I'm wrong.
I raise #2380 for it.
A valid OData WebAPI query generates an invalid SQLite command that has a syntax error. I'm using EFCore SQLite with Odata.
Assemblies affected
TargetFramework - netcoreapp3.1 SDK - Microsoft.NET.Sdk.Web Microsoft.AspNetCore.OData - v7.5.2 Microsoft.EntityFrameworkCore.Sqlite - v3.1.10
Reproduce steps
URL:
https://localhost:5001/OData/Customers('1AA1049')/BagItems?$expand=Item($expand=UnitOfMeasure,Prices($filter=PriceListCode eq 'MAIN'))
Expected result
No SqliteException
Actual result
SQL command with invalid syntax.
Update - 1
https://localhost:5001/OData/Customers('1AA1049')/BagItems?$expand=Item($expand=UnitOfMeasure)
workshttps://localhost:5001/OData/Customers('1AA1049')/BagItems?$expand=Item($expand=Prices)
same error aboveSummary: Get - BagItem Expand - Item (Object) Then Expand -- Prices (IEnumerable) *Error
Update - 2
/Items?$expand=Barcodes
same error above/Items?$expand=Prices
same error aboveIt looks like it only happens when querying an EntitySet and expanding a collection property in the EntitySet
Update - 3
Related issues: https://github.com/dotnet/efcore/issues/17230 https://github.com/dotnet/efcore/issues/19178
My conclusion: Joining two tables by using expand yields an expression that compiles into an SQL statement that uses outer apply to join or to sub-query and errors because outer apply is not supported in SQLite.