Querying OData entityset with expanding collection property generates SqliteException

[AliAlmutawakel]

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'))

        // controller endpoint

        private readonly AppDbContext _db; // has public DbSet<BagItem> BagItems property

        [HttpGet]
        [EnableQuery(PageSize = 20)]
        [ODataRoute("Customers({keyCustomerId})/BagItems")]
        public IQueryable<BagItem> GetBagItems([FromODataUri] string keyCustomerId)
            => _db.BagItems.Where(bagItem => bagItem.CustomerId.Equals(keyCustomerId));

    // schema
    public abstract class EntityBase
    {
        // note: had to set default value in migration as DateTime.UtcNow
        [Required]
        [DataType(DataType.DateTime)]
        [DefaultValue("GETUTCDATE()")]
        public DateTime CreatedUtcDate { get; set; } = DateTime.UtcNow;

        [Required]
        [DataType(DataType.DateTime)]
        [DefaultValue("GETUTCDATE()")]
        public DateTime UpdatedUtcDate { get; set; } = DateTime.UtcNow;
    }
    [Table("BagItems")]
    public class BagItem : EntityBase
    {
        [CompositeKey]
        [ForeignKey("Customer")]
        [Required]
        [Key]
        public string CustomerId { get; set; }

        public Customer Customer { get; set; }

        [CompositeKey]
        [ForeignKey("Item")]
        [Required]
        [Key]
        public string ItemId { get; set; }

        public Item Item { get; set; }

        [Required]
        public int Quantity { get; set; }

        public string Comment { get; set; }

        public string Instruction { get; set; }
    }

    [Table("Items")]
    public class Item : EntityBase
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public string Id { get; set; }

        [Required]
        public string Description { get; set; }

        public string Comment { get; set; }

        [Url]
        [DataType(DataType.ImageUrl)]
        public string ImageUrl { get; set; }

        public UnitOfMeasure UnitOfMeasure { get; set; }

        [Required]
        [ForeignKey("UnitOfMeasure")]
        public string UnitOfMeasureCode { get; set; }

        [Required]
        [EnumDataType(typeof(LineType))]
        public LineType LineType { get; set; } = LineType.Item;

        [Required]
        public IEnumerable<ItemPricing> Prices { get; set; }

        public IEnumerable<LocationInventory> Inventory { get; set; }

        public IEnumerable<ItemBarcodes> Barcodes { get; set; }

        public ItemCategory ItemCategory { get; set; }

        [ForeignKey("ItemCategory")]
        [Required]
        public string Segment1 { get; set; }

        [Required]
        public string Segment2 { get; set; }

        [Required]
        public string Segment3 { get; set; }
    }

Expected result

No SqliteException

Actual result

SQL command with invalid syntax.

2020-11-24 12:24:20.180 -07:00 [ERR] (Microsoft.EntityFrameworkCore.Database.Command) Failed executing DbCommand (4ms) [Parameters=[@__keyCustomerId_0='?' (Size = 8), @__TypedProperty_7='?', @__TypedProperty_6='?' (Size = 36), @__TypedProperty_4='?' (Size = 4), @__TypedProperty_5='?'], CommandType='Text', CommandTimeout='30']
SELECT "t"."CustomerId", "t"."ItemId", "t"."Comment", "t"."CreatedUtcDate", "t"."Instruction", "t"."Quantity", "t"."UpdatedUtcDate", "t"."Id", "t"."Comment0", "t"."CreatedUtcDate0", "t"."Description", "t"."ImageUrl", "t"."LineType", "t"."Segment1", "t"."Segment2", "t"."Segment3", "t"."UnitOfMeasureCode", "t"."UpdatedUtcDate0", "t"."Code", "t"."CreatedUtcDate1", "t"."Description0", "t"."UpdatedUtcDate1", "t"."c", "t"."c0", "t0"."c", "t0"."ItemId", "t0"."PriceListCode", "t0"."CreatedUtcDate", "t0"."SalesEndUtcDate", "t0"."SalesPrice", "t0"."SalesStartUtcDate", "t0"."UnitPrice", "t0"."UpdatedUtcDate", "t0"."c0"
FROM (
    SELECT "b"."CustomerId", "b"."ItemId", "b"."Comment", "b"."CreatedUtcDate", "b"."Instruction", "b"."Quantity", "b"."UpdatedUtcDate", "i"."Id", "i"."Comment" AS "Comment0", "i"."CreatedUtcDate" AS "CreatedUtcDate0", "i"."Description", "i"."ImageUrl", "i"."LineType", "i"."Segment1", "i"."Segment2", "i"."Segment3", "i"."UnitOfMeasureCode", "i"."UpdatedUtcDate" AS "UpdatedUtcDate0", "u"."Code", "u"."CreatedUtcDate" AS "CreatedUtcDate1", "u"."Description" AS "Description0", "u"."UpdatedUtcDate" AS "UpdatedUtcDate1", 0 AS "c", 0 AS "c0"
    FROM "BagItems" AS "b"
    INNER JOIN "Items" AS "i" ON "b"."ItemId" = "i"."Id"
    INNER JOIN "UnitsOfMeasure" AS "u" ON "i"."UnitOfMeasureCode" = "u"."Code"
    WHERE "b"."CustomerId" = @__keyCustomerId_0
    ORDER BY "b"."CustomerId", "b"."ItemId"
) AS "t"
    LIMIT @__TypedProperty_7
OUTER APPLY (
    SELECT @__TypedProperty_6 AS "c", "i0"."ItemId", "i0"."PriceListCode", "i0"."CreatedUtcDate", "i0"."SalesEndUtcDate", "i0"."SalesPrice", "i0"."SalesStartUtcDate", "i0"."UnitPrice", "i0"."UpdatedUtcDate", 1 AS "c0"
    FROM "ItemsPricing" AS "i0"
    WHERE ("t"."Id" = "i0"."ItemId") AND ("i0"."PriceListCode" = @__TypedProperty_4)
    ORDER BY "i0"."ItemId", "i0"."PriceListCode"
    LIMIT @__TypedProperty_5
) AS "t0"
ORDER BY "t"."CustomerId", "t"."ItemId", "t"."Id", "t"."Code", "t0"."ItemId", "t0"."PriceListCode"
2020-11-24 12:24:20.235 -07:00 [ERR] (Microsoft.EntityFrameworkCore.Query) An exception occurred while iterating over the results of a query for context type 'MBS.SalesApp.Infrastructure.Data.AppDbContext'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "(": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "(": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
2020-11-24 12:24:20.245 -07:00 [INF] (Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker) Executed action MBS.Web.Controllers.CustomersController.GetBagItemsAsync (MBS.SalesApp.Web) in 371.0755ms
Exception thrown: 'System.Reflection.TargetInvocationException' in System.Private.CoreLib.dll
Loaded '/usr/local/share/dotnet/shared/Microsoft.NETCore.App/3.1.6/System.IO.MemoryMappedFiles.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
2020-11-24 12:24:21.157 -07:00 [WRN] (MBS.SalesApp.Web.Middleware.ExceptionHandlerMiddleware) Unhandled type of exception: TargetInvocationException
2020-11-24 12:24:21.159 -07:00 [FTL] (MBS.SalesApp.Web.Middleware.ExceptionHandlerMiddleware) Something went wrong: Exception has been thrown by the target of an invocation.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "(": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.LimitResults[T](IQueryable`1 queryable, Int32 limit, Boolean parameterize, Boolean& resultsLimited)
   at Microsoft.AspNet.OData.Query.TruncatedCollection`1..ctor(IQueryable`1 source, Int32 pageSize, Boolean parameterize)
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.LimitResults(IQueryable queryable, Int32 limit, Boolean parameterize, Boolean& resultsLimited)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.ApplyPaging(IQueryable result, ODataQuerySettings querySettings)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.ApplyTo(IQueryable query, ODataQuerySettings querySettings)
   at Microsoft.AspNet.OData.EnableQueryAttribute.ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
   at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func`2 modelFunction, IWebApiRequestMessage request, Func`2 createQueryOptionFunction)
   at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func`2 modelFunction, Func`2 createQueryOptionFunction, Action`1 createResponseAction, Action`3 createErrorAction)
   at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(ActionExecutedContext actionExecutedContext)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Filters.ActionFilterAttribute.OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)
   at IdentityServer4.Hosting.MutualTlsTokenEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.Invoke(HttpContext context)
   at MBS.SalesApp.Web.Middleware.ExceptionHandlerMiddleware.InvokeAsync(HttpContext httpContext) in /Users/ipodserver2017/Projects/SALES-APP/MBS.SalesApp/src/MBS.SalesApp.Web/Middleware/ExceptionHandlerMiddleware.cs:line 27
2020-11-24 12:24:21.166 -07:00 [INF] (Microsoft.AspNetCore.Hosting.Diagnostics) Request finished in 1322.4968ms 500 application/json

---

Update - 1

https://localhost:5001/OData/Customers('1AA1049')/BagItems?$expand=Item($expand=UnitOfMeasure) works
https://localhost:5001/OData/Customers('1AA1049')/BagItems?$expand=Item($expand=Prices) same error above

Summary:
Get - BagItem
Expand - Item (Object)
Then Expand -- Prices (IEnumerable) *Error

Update - 2

/Items?$expand=Barcodes same error above
/Items?$expand=Prices same error above

It looks like it only happens when querying an EntitySet and expanding a collection property in the EntitySet

Update - 3

Related issues:
dotnet/efcore#17230
dotnet/efcore#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.

[anranruye]

@AliAlmutawakel It's probably a problem of EF rather than ODataWebApi. Upgrade to ef core 5 and use AsSplitQuery() may help.

[AliAlmutawakel]

@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?

[anranruye]

@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.

[AliAlmutawakel]

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: dotnet/efcore#19178 (comment) where the LINQ expression creates a Join command instead of outer apply.

[qazq]

@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.

[anranruye]

@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.