Known limitation: Invalid SQL is produced in v4.x

[bart-degreed]

Symptons

When using certain kinds of relationships in combination with both top-level and nested paging, EF Core produces an invalid SQL statement. This bug in EF Core is tracked here.

Publicly logged cases:

• Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near \"(\": syntax error'.
  Sqlite with many-to-many relationship, Include Query Error Not Working #861
• Microsoft.Data.SqlClient.SqlException: Invalid column name 'RoleId'
  SQL Server with many-to-many relationship, Malformed Query when GetAsync() and including a navigation collection with HasManyThrough #884
• Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near \"(\": syntax error'.
  Sqlite with one-to-many relationship, Failed to get resource with relationships when using Entity Framework Core Sqlite #918
• Npgsql.PostgresException (0x80004005): 42703: column t.TagId does not exist.
  PostgreSQL and SQL Server with many-to-many relationship, Nested inclusion leads to error: An unhandled error occurred while processing this request. #920

When this happens, the produced SQL usually contains an OUTER APPLY or LEFT JOIN LATERAL clause.

Environment

JsonApiDotNetCore: v4.x
EF Core: v3.1.x and v5
Database providers: SQL Server, SQLLocalDB, Sqlite, PostgreSQL

Update (April 4th, 2021)
This issue was reported fixed in EF Core 6 Preview 3 (verified here). Please let us know in the comments below if you're still experiencing this.

Background

In v4-beta1, we redesigned the generation of the LINQ expressions that we feed to EF Core. Among other things, we added paging
for non-primary endpoints (/articles/1/authors) and for includes (/articles?include=authors).
Because paging is enabled by default, this generates a LINQ expression with multiple Skip/Take clauses.
In most cases that works fine, but occasionally it causes the errors listed above.

The EF Core team is aware of this problem and recently proposed an internal design change at dotnet/efcore#17337 (comment) that's going to solve this. Aside from us, the OData team (Microsoft) reported the same problem at dotnet/efcore#19763, which contains a comment from an EF Core team member saying the underlying issue is committed to be fixed in EF Core 6.0.

If this fix is important to you, please upvote the issue so it gets prioritized.

Workarounds

1. Disable nested paging in the request, for example:

GET http://localhost:14141/userprofiles?include=roles&page[size]=roles:0 HTTP/1.1

2. Disable default paging in .AddJsonApi(...) from Startup.cs:

options.DefaultPageSize = null;

Note this makes simple requests succeed, but still fails when paging at both levels is explicitly asked for:

GET http://localhost:14140/articles/?include=tags&page[size]=1,tags:1 HTTP/1.1

3. Add a custom resource definition that deactivates nested paging at all endpoints:

public class OnlyTopPaginationDefinition<TResource, TId> : JsonApiResourceDefinition<TResource, TId>
    where TResource : class, IIdentifiable<TId>
{
    private readonly IJsonApiRequest _request;

    public OnlyTopPaginationDefinition(IResourceGraph resourceGraph, IJsonApiRequest request)
        : base(resourceGraph)
    {
        _request = request ?? throw new ArgumentNullException(nameof(request));
    }

    public override PaginationExpression OnApplyPagination(PaginationExpression existingPagination)
    {
        var resourceContext = ResourceGraph.GetResourceContext<TResource>();

        if (resourceContext != _request.PrimaryResource)
        {
            return new PaginationExpression(PageNumber.ValueOne, pageSize: null);
        }

        return base.OnApplyPagination(existingPagination);
    }
}

public class OnlyTopPaginationDefinition<TResource>
    : OnlyTopPaginationDefinition<TResource, int>, IResourceDefinition<TResource>
    where TResource : class, IIdentifiable<int>
{
    public OnlyTopPaginationDefinition(IResourceGraph resourceGraph, IJsonApiRequest request)
        : base(resourceGraph, request)
    {
    }
}

and register it from Startup.cs, after .AddJsonApi(...):

services.AddScoped(typeof(IResourceDefinition<,>), typeof(OnlyTopPaginationDefinition<,>));
services.AddScoped(typeof(IResourceDefinition<>), typeof(OnlyTopPaginationDefinition<>));

Instead of registering the unconstrained generic, you can inherit and register resource-specific subclasses if you want to control this per resource type.

[UpQuark]

Thanks for the writeup. Experiencing this with nested requests for "HasOne" relationships, on 4.1.1, e.g.

GET /organizations?include=roles.user

...where Organizations HasMany Roles, and each Role HasOne Organization and HasOne User. It gives me "Invalid Column Name" errors from a malformed Join which is looking for columns of the intermediate model (Role) in the table of the source model (Organization).

Disabling paging seems to have resolved it. I don't see the behavior requesting nested HasMany relationships.

[ThomasBarnekow]

@bart-degreed, thanks for documenting this so nicely. I've run into the same issue. The reported error was like your second bullet (invalid column name). I've successfully used the first workaround.

[bkoelman]

Closing, as there's nothing we can do to make this work on old versions. The bug was fixed in EF Core 6.