Failed to get resource with relationships when using Entity Framework Core Sqlite

[goatvn]

DESCRIPTION

I tried to get the resource by id with including relationship but I got http status code 500 (Internal Server Error). The response I got is :
{ "errors": [ { "id": "06690e41-98f5-4f48-abaf-378434496c74", "status": "500", "title": "An unhandled error occurred while processing this request.", "detail": "SQLite Error 1: 'near \"(\": syntax error'.", "meta": { "stackTrace": [ "Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near \"(\": syntax error'.\r", " at void Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db)\r", " at IEnumerable<sqlite3_stmt> Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()\r", " at IEnumerable<sqlite3_stmt> Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()\r", " at bool Microsoft.Data.Sqlite.SqliteDataReader.NextResult()\r", " at SqliteDataReader Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)\r", " at Task<SqliteDataReader> Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)\r", " at async Task<DbDataReader> Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)\r", " at async Task<RelationalDataReader> Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r", " at async Task<RelationalDataReader> Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r", " at async Task<RelationalDataReader> Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r", " at async Task<bool> Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(DbContext _, bool result, CancellationToken cancellationToken)\r", " at async ValueTask<bool> Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()\r", " at async Task<List<TSource>> Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)\r", " at async Task<List<TSource>> Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)\r", " at async Task<IReadOnlyCollection<TResource>> JsonApiDotNetCore.Repositories.EntityFrameworkCoreRepository<TResource, TId>.GetAsync(QueryLayer layer, CancellationToken cancellationToken) in C:/Users/admin/Downloads/JsonApiDotNetCore-master (1)/JsonApiDotNetCore-master/src/JsonApiDotNetCore/Repositories/EntityFrameworkCoreRepository.cs:line 62\r", " at object CallSite.Target(Closure, CallSite, object)\r", " at TRet System.Dynamic.UpdateDelegates.UpdateAndExecute1<T0, TRet>(CallSite site, T0 arg0)\r", " at async Task<IReadOnlyCollection<TResource>> JsonApiDotNetCore.Repositories.ResourceRepositoryAccessor.GetAsync<TResource>(QueryLayer layer, CancellationToken cancellationToken) in C:/Users/admin/Downloads/JsonApiDotNetCore-master (1)/JsonApiDotNetCore-master/src/JsonApiDotNetCore/Repositories/ResourceRepositoryAccessor.cs:line 30\r", " at async Task<TResource> JsonApiDotNetCore.Services.JsonApiResourceService<TResource, TId>.TryGetPrimaryResourceByIdAsync(TId id, TopFieldSelection fieldSelection, CancellationToken cancellationToken) in C:/Users/admin/Downloads/JsonApiDotNetCore-master (1)/JsonApiDotNetCore-master/src/JsonApiDotNetCore/Services/JsonApiResourceService.cs:line 423\r", " at async Task<TResource> JsonApiDotNetCore.Services.JsonApiResourceService<TResource, TId>.GetAsync(TId id, CancellationToken cancellationToken) in C:/Users/admin/Downloads/JsonApiDotNetCore-master (1)/JsonApiDotNetCore-master/src/JsonApiDotNetCore/Services/JsonApiResourceService.cs:line 94\r", " at async Task<IActionResult> JsonApiDotNetCore.Controllers.BaseJsonApiController<TResource, TId>.GetAsync(TId id, CancellationToken cancellationToken) in C:/Users/admin/Downloads/JsonApiDotNetCore-master (1)/JsonApiDotNetCore-master/src/JsonApiDotNetCore/Controllers/BaseJsonApiController.cs:line 114\r", " at async Task<IActionResult> JsonApiDotNetCore.Controllers.JsonApiController<TResource, TId>.GetAsync(TId id, CancellationToken cancellationToken) in C:/Users/admin/Downloads/JsonApiDotNetCore-master (1)/JsonApiDotNetCore-master/src/JsonApiDotNetCore/Controllers/JsonApiController.cs:line 58\r", " at async ValueTask<IActionResult> Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)\r", " at TResult System.Runtime.CompilerServices.ValueTaskAwaiter<TResult>.GetResult()\r", " at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()+Awaited(?)\r", " at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()+Awaited(?)\r", " at void Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r", " at Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)\r", " at Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()\r", " at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeNextExceptionFilterAsync()+Awaited(?)" ] } } ] }

I tried with the latest JsonApiDotnetCore and Entity Framework Core Sqlite.

STEPS TO REPRODUCE

Models:

public class Role : Identifiable
    {
        [Attr]
        public string Name { get; set; }

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

public class Group : Identifiable
    {
        [Attr]
        public string GroupName { get; set; }

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

        [HasMany]
        public ICollection<Role> Roles { get; set; }
    }

DbContext:

public class SqliteAppDbContext : DbContext
    {
        public ISystemClock SystemClock { get; }

        public DbSet<Group> Groups { get; set; }
        public DbSet<Role> Roles { get; set; }

        public SqliteAppDbContext(DbContextOptions<SqliteAppDbContext> options, ISystemClock systemClock) : base(options)
        {
            SystemClock = systemClock ?? throw new ArgumentNullException(nameof(systemClock));
        }        
    }

Startup:

public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public virtual void ConfigureServices(IServiceCollection services)
        {
            //options.UseLoggerFactory(this.LoggerFactory);
            services.AddSingleton<ISystemClock, SystemClock>();
            //services.AddControllers();
            //var testDb = Guid.NewGuid().ToString();
            var connection = new SqliteConnection("DataSource=sqlitedemo.db");
            connection.Open();
            services.AddSingleton(connection);

            services.AddDbContext<SqliteAppDbContext>(options =>
            {
                options.UseSqlite(connection);
                //options.UseLoggerFactory(loggerFactory);
                options.EnableDetailedErrors();
                options.EnableSensitiveDataLogging();
            });
            services.AddJsonApi<SqliteAppDbContext>(ConfigureJsonApiOptions, discovery => discovery.AddCurrentAssembly());

            services.AddClientSerialization();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();
            app.UseJsonApi();
            app.UseEndpoints(endpoints => endpoints.MapControllers());
        }

        protected virtual void ConfigureJsonApiOptions(JsonApiOptions options)
        {
            options.IncludeExceptionStackTraceInErrors = true;
            options.Namespace = "api/v1";
            options.DefaultPageSize = new PageSize(5);
            options.IncludeTotalResourceCount = true;
            options.ValidateModelState = true;
            options.SerializerSettings.Formatting = Formatting.Indented;
            options.SerializerSettings.Converters.Add(new StringEnumConverter());
        }
    }

The sql script was generated when queried the group:

Executing DbCommand [Parameters=[@__Create_Item1_1='5' (DbType = String), @__Create_Item1_0='1' (DbType = String)], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Id", "g"."Description", "g"."GroupName", "t"."Id", "t"."Description", "t"."GroupId", "t"."Name"
      FROM "Groups" AS "g"
      OUTER APPLY (
          SELECT "r"."Id", "r"."Description", "r"."GroupId", "r"."Name"
          FROM "Roles" AS "r"
          WHERE "g"."Id" = "r"."GroupId"
          ORDER BY "r"."Id"
          LIMIT @__Create_Item1_1
      ) AS "t"
      WHERE "g"."Id" = @__Create_Item1_0
      ORDER BY "g"."Id", "t"."Id"

Query:

• Http method: GET
• Url: api/v1/groups/1?include=roles

EXPECTED BEHAVIOR

Can get the group (id=1) with its roles.

ACTUAL BEHAVIOR

Error with "SQLite Error 1: 'near "(": syntax error'."

VERSIONS USED

• JsonApiDotNetCore version: 4.0.1
• ASP.NET Core version: 3.1.0
• Entity Framework Core version: 3.1.10
• Entity Framework Core Sqlite version: 3.1.10
• Database provider: Sqlite

[bart-degreed]

Thanks for the detailed report!

It looks like EF Core generates invalid SQL, which is either a bug or a limitation in the Sqlite provider. I suspect the former, as I have seen similar cases with OUTER APPLY being wrong. There's a workaround you might want to try, see #861 (comment).

Other than that, it's really up to the EF Core team to fix this. If this matters to you, please upvote the issue at the EF Core repo to get it prioritized.

[goatvn]

Hi @bart-degreed .

I'll try it. Thanks for your reply.

[goatvn]

Hi @bart-degreed ,

I followed #861 and it worked. If that is an EF Core issue, I wonder why it worked as expected with JANC 4.0.0-alpha5. I have tried with this release before and everything worked fine. Thanks.

[bart-degreed]

It worked in JADNC v4-alpha5 because it did not provide nested paging. In #792 (released in v4-beta1), we redesigned the way LINQ expressions are built, to fix bugs and enable various new features. For example, in alpha5 a request like /blogs/1/articles would always return all related articles, which is unacceptable for large tables in production scenarios.

I'm going to leave this issue open to remind myself to create a 'known limitations' issue that I can reference to in future reports.

[goatvn]

I got it. Thanks so much, @bart-degreed .

[bart-degreed]

Created #922, which contains another workaround.