SQLite: Unable to handle Take nested in Select

[TobiasWolters]

Using Take nested in a select with a one-to-many relationship generates invalid SQL for SQLite.

To Reproduce

I have two classes: A and B with a one-to-many relationship. E.g.:

public class A {
     public int Id { get; set; }
     public IEnumerable<B> Bs { get; set; } 
}

public class B {
    public int Id { get; set; }
    public A TheA { get; set; }
    public int AId { get; set; }
}

The DbContext class contains the following:

public DbSet<A> As { get; set; }
public DbSet<B> Bs { get; set; }

A database table exists for each entity.

I then execute the following query:

var selectedAs = await context.As.Select(a => new A {
   Id = a.Id,
   Bs = a.Bs.Take(n)
}).ToListAsync();

, where n is an integer.

With SQLServer, everything works fine. However, when running the query in unit tests (SQLite), I get the following error:

fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type <DBContextClass>.
      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.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
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.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

Note

It manages to generate SQL, but the SQL seems to be invalid for SQLite.

Additional context

Microsoft.Data.Sqlite version: 3.1.4
Target framework: .NET Core 3.1
Operating system: Windows 10 Pro.

[maumar]

dupe of #19178

[smitpatel]

@maumar - Looking at the query, it should get converted to JOIN rather than APPLY.

[PascalTurbo]

I got an issue that may be related to this one. The interesting thing is, that it only occurs on Windows, not on MacOS.

List<ForecastSummaryDetailsDTO> forecastSummaryDetails = await moduleContext.Database.ForecastItems
    .Where(i => i.ExpectedDate == expectedDate && i.ProductId == productId)
    .Select(i =>
        new ForecastSummaryDetailsDTO()
        {
            ForecastItemId = i.Id,
            Forecast = Database.Forecasts.First(f => f.Id == i.ForecastId)
            Quantity = i.Quantity
        }).ToListAsync();

The selection of Forecast = Database.Forecasts.First(f => f.Id == i.ForecastId) leads to the same error as @TobiasNissen reported.

[smitpatel]

Related #24474