Cannot Change HasMany Relationship

[ThomasBarnekow]

DESCRIPTION

I am trying to update a HasMany relationship between one WorkingTree and many Document resources as described in the documentation under Changing HasMany relationships. I am currently testing this with Postman, where {{url}} is the server URL and {{working_tree_id}} and {{document_id}} are Guids of existing WorkingTree and Document entities, respectively:

POST {{url}}/workingTrees/{{working_tree_id}}/relationships/documents
{   
    "data": [
        {
            "type": "documents",
            "id": "{{document_id}}"
        }
    ]
}

However, that leads to the following exception:

fail: JsonApiDotNetCore.Middleware.ExceptionHandler[0]
      Failed to persist changes in the underlying data store.
      JsonApiDotNetCore.Repositories.DataStoreUpdateException: Failed to persist changes in the underlying data store.
       ---> Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
         at void Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(int commandIndex, int expectedRowsAffected, int rowsAffected)
         at async Task<int> Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagationAsync(int commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
         at async Task Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
         at async Task Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) x 2
         at async Task<int> Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable<ModificationCommandBatch> commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) x 3
         at async Task<int> Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList<IUpdateEntry> entriesToSave, CancellationToken cancellationToken) x 2
         at async Task<TResult> Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
         at async Task<int> Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken) x 2
         at async Task JsonApiDotNetCore.Repositories.EntityFrameworkCoreRepository<TResource, TId>.SaveChangesAsync(CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Repositories/EntityFrameworkCoreRepository.cs:line 454
         --- End of inner exception stack trace ---
         at async Task JsonApiDotNetCore.Repositories.EntityFrameworkCoreRepository<TResource, TId>.SaveChangesAsync(CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Repositories/EntityFrameworkCoreRepository.cs:line 465
         at async Task JsonApiDotNetCore.Repositories.EntityFrameworkCoreRepository<TResource, TId>.AddToToManyRelationshipAsync(TId primaryId, ISet<IIdentifiable> secondaryResourceIds, CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Repositories/EntityFrameworkCoreRepository.cs:line 371
         at void System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid1<T0>(CallSite site, T0 arg0)
         at async Task JsonApiDotNetCore.Repositories.ResourceRepositoryAccessor.AddToToManyRelationshipAsync<TResource, TId>(TId primaryId, ISet<IIdentifiable> secondaryResourceIds, CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Repositories/ResourceRepositoryAccessor.cs:line 114
         at async Task JsonApiDotNetCore.Services.JsonApiResourceService<TResource, TId>.AddToToManyRelationshipAsync(TId primaryId, string relationshipName, ISet<IIdentifiable> secondaryResourceIds, CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Services/JsonApiResourceService.cs:line 306
         at async Task JsonApiDotNetCore.Services.JsonApiResourceService<TResource, TId>.AddToToManyRelationshipAsync(TId primaryId, string relationshipName, ISet<IIdentifiable> secondaryResourceIds, CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Services/JsonApiResourceService.cs:line 314
         at async Task<IActionResult> JsonApiDotNetCore.Controllers.BaseJsonApiController<TResource, TId>.PostRelationshipAsync(TId id, string relationshipName, ISet<IIdentifiable> secondaryResourceIds, CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Controllers/BaseJsonApiController.cs:line 243
         at async Task<IActionResult> JsonApiDotNetCore.Controllers.JsonApiController<TResource, TId>.PostRelationshipAsync(TId id, string relationshipName, ISet<IIdentifiable> secondaryResourceIds, CancellationToken cancellationToken) in C:/projects/jsonapidotnetcore/src/JsonApiDotNetCore/Controllers/JsonApiController.cs:line 83
         at async ValueTask<IActionResult> Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
         at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()+Awaited(?)
         at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()+Awaited(?)
         at void Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
         at Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
         at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()+Awaited(?)
         at async Task Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeNextExceptionFilterAsync()+Awaited(?)

What works is the following:

PATCH {{url}}/documents/{{document_id}}/relationships/workingTree
{   
    "data": {
        "type": "workingTrees",
        "id": "{{working_tree_id}}"
    }
}

STEPS TO REPRODUCE

Here's the WorkingTree definition, where EntityBase<Guid> is an Identifiable<Guid> with some default properties added to every resource.

    public class WorkingTree : EntityBase<Guid>
    {
        // Data

        /// <inheritdoc />
        [Attr]
        [MaxLength(90)]
        public string Sha256 { get; set; }

        // Navigation Properties

        /// <inheritdoc />
        [HasMany]
        public ICollection<Document> Documents { get; set; }
    }

    public class Document : EntityBase<Guid>
    {
        // Foreign Keys (simplified)

        public Guid? WorkingTreeId { get; set; }

        [HasOne]
        [CanBeNull]
        public WorkingTree WorkingTree { get; set; }

        // Attributes removed to simplify
    }

EXPECTED BEHAVIOR

The HasMany relationship can be changed as documented.

ACTUAL BEHAVIOR

Changing the HasMany relationship as documented throws an exception.

VERSIONS USED

• JsonApiDotNetCore version: 4.1.1
• ASP.NET Core version: 5.0
• Entity Framework Core version: 5.0
• Database provider: SQL Server Local DB and Azure SQL Database (same error)

[bart-degreed]

Can you post the produced SQL?

To make it visible in logs, add this to appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

[ThomasBarnekow]

@bart-degreed, here is the relevant part of the log, including the initial part of the exception message. The rest of that is the same as in my original description.

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (71ms) [Parameters=[@p2='?' (DbType = Guid), @p0='?' (DbType = DateTimeOffset), @p3='?' (Size = 8) (DbType = Binary), @p1='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [Documents] SET [DateModified] = @p0, [WorkingTreeId] = @p1
      WHERE [Id] = @p2 AND [RowVersion] IS NULL;
      SELECT [RowVersion]
      FROM [Documents]
      WHERE @@ROWCOUNT = 1 AND [Id] = @p2;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[@__Create_Item1_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT [w].[Id]
      FROM [WorkingTrees] AS [w]
      WHERE [w].[Id] = @__Create_Item1_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__Create_Item1_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id]
      FROM [Documents] AS [d]
      WHERE [d].[Id] = @__Create_Item1_0
fail: JsonApiDotNetCore.Middleware.ExceptionHandler[0]
      Failed to persist changes in the underlying data store.
      JsonApiDotNetCore.Repositories.DataStoreUpdateException: Failed to persist changes in the underlying data store.
       ---> Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Looking at this, [RowVersion] IS NULL in the WHERE clause does not look right, because RowVersion is never null.

The RowVersion property is defined in my EntityBase<TId> class as follows:

    public abstract class EntityBase<TId> : Identifiable<TId>
    {
        [UsedImplicitly]
        public DateTimeOffset? DateCreated { get; set; }

        [UsedImplicitly]
        public DateTimeOffset? DateModified { get; set; }

        /// <summary>
        /// Gets or sets the concurrency token.
        /// </summary>
        [UsedImplicitly]
        [Timestamp]
        public byte[] RowVersion { get; set; }
    }

The table definition (from which I removed columns that are not relevant for this issue) looks like this:

CREATE TABLE [dbo].[Documents] (
    [Id]                UNIQUEIDENTIFIER   NOT NULL,
    [WorkingTreeId]     UNIQUEIDENTIFIER   NULL,
    [DateCreated]       DATETIMEOFFSET (7) NULL,
    [DateModified]      DATETIMEOFFSET (7) NULL,
    [RowVersion]        ROWVERSION         NULL,
    ...
);

The DateCreated, DateModified, and RowVersion columns are all defined by convention. Is the issue related to RowVersion being nullable?

[bart-degreed]

Yes, this fails because of RowVersion. This currently does not work. We have an open issue on our roadmap to enable support for clients to send the version and make optimistic concurrency work.

Until then, I recommend to remove it, because it doesn't help much right now.

For efficiency, Delete Resource and Post ToMany Relationship endpoints do not fetch everything, so the row version is never retrieved.

If you insist on using RowVersion right now, at the very least you'd need to override DeleteAsync and AddToToManyRelationshipAsync on EntityFrameworkCoreRepository to fetch the unloaded resources into the change tracker upfront. I've never tried that, though.

[ThomasBarnekow]

OK, I should be able to remove the RowVersion property for now.

[bart-degreed]

Concerning RowVersion, see our proposal at #1004. Feel free to express your opinion about the proposal by adding a comment to it.