EF doesn't like a 1:many cascade delete

David Thielen 3,186 Reputation points
2023-07-06T21:36:27.2+00:00

Hi all;

I am getting the error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Signups_Events_EventId' on table 'Signups' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

This makes no sense to me as this is a clean 1:many relationship.

public class Signup
{
    public int Id { get; private set; }
    public AppUser User { get; set; }
    public Event Event { get; set; }
}

public class Event
{
    public int Id { get; private set; }
    public required AppUser Owner { get; set; }
    public ICollection<Signup>? Signups { get; set; }
}

public class AppUser : IAuditTrail, IPrimaryEntities
{
    public ICollection<Event>? Events { get; set; }
    public ICollection<Signup>? Signups { get; set; }
}

How/why is it unhappy with the Event : Signup relationship? This appears to me to be a very simple 1:many relationship where a delete of an Event should cascade and delete the signups.

Is there something I need to do to then remove those signups from AppUser.Signups? Shouldn't those be cascade deleted too?

Full error message:

Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Signups] (
    [Id] int NOT NULL IDENTITY,
    [UserId] int NOT NULL,
    [EventId] int NOT NULL,
    [RoiRating] int NOT NULL,
    [EnjoyRating] int NOT NULL,
    [VolunteerRating] int NOT NULL,
    [Summarization] nvarchar(max) NULL,
    [Closed] bit NOT NULL,
    [Deleted] bit NOT NULL,
    [Created] datetime2 NOT NULL,
    [RowVersion] rowversion NOT NULL,
    CONSTRAINT [PK_Signups] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Signups_AppUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AppUsers] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Signups_Events_EventId] FOREIGN KEY ([EventId]) REFERENCES [Events] ([Id]) ON DELETE CASCADE
);
{
  "Timestamp": "21:14:22 ",
  "EventId": 20102,
  "LogLevel": "Error",
  "Category": "Microsoft.EntityFrameworkCore.Database.Command",
  "Message": "Failed executing DbCommand (12ms) [Parameters=[], CommandType=\u0027Text\u0027, CommandTimeout=\u002730\u0027]\r\nCREATE TABLE [Signups] (\r\n    [Id] int NOT NULL IDENTITY,\r\n    [UserId] int NOT NULL,\r\n    [EventId] int NOT NULL,\r\n    [RoiRating] int NOT NULL,\r\n    [EnjoyRating] int NOT NULL,\r\n    [VolunteerRating] int NOT NULL,\r\n    [Summarization] nvarchar(max) NULL,\r\n    [Closed] bit NOT NULL,\r\n    [Deleted] bit NOT NULL,\r\n    [Created] datetime2 NOT NULL,\r\n    [RowVersion] rowversion NOT NULL,\r\n    CONSTRAINT [PK_Signups] PRIMARY KEY ([Id]),\r\n    CONSTRAINT [FK_Signups_AppUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AppUsers] ([Id]) ON DELETE CASCADE,\r\n    CONSTRAINT [FK_Signups_Events_EventId] FOREIGN KEY ([EventId]) REFERENCES [Events] ([Id]) ON DELETE CASCADE\r\n);",
  "State": {
    "Message": "Failed executing DbCommand (12ms) [Parameters=[], CommandType=\u0027Text\u0027, CommandTimeout=\u002730\u0027]\r\nCREATE TABLE [Signups] (\r\n    [Id] int NOT NULL IDENTITY,\r\n    [UserId] int NOT NULL,\r\n    [EventId] int NOT NULL,\r\n    [RoiRating] int NOT NULL,\r\n    [EnjoyRating] int NOT NULL,\r\n    [VolunteerRating] int NOT NULL,\r\n    [Summarization] nvarchar(max) NULL,\r\n    [Closed] bit NOT NULL,\r\n    [Deleted] bit NOT NULL,\r\n    [Created] datetime2 NOT NULL,\r\n    [RowVersion] rowversion NOT NULL,\r\n    CONSTRAINT [PK_Signups] PRIMARY KEY ([Id]),\r\n    CONSTRAINT [FK_Signups_AppUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AppUsers] ([Id]) ON DELETE CASCADE,\r\n    CONSTRAINT [FK_Signups_Events_EventId] FOREIGN KEY ([EventId]) REFERENCES [Events] ([Id]) ON DELETE CASCADE\r\n);",
    "elapsed": "12",
    "parameters": "",
    "commandType": "Text",
    "commandTimeout": 30,
    "newLine": "\r\n",
    "commandText": "CREATE TABLE [Signups] (\r\n    [Id] int NOT NULL IDENTITY,\r\n    [UserId] int NOT NULL,\r\n    [EventId] int NOT NULL,\r\n    [RoiRating] int NOT NULL,\r\n    [EnjoyRating] int NOT NULL,\r\n    [VolunteerRating] int NOT NULL,\r\n    [Summarization] nvarchar(max) NULL,\r\n    [Closed] bit NOT NULL,\r\n    [Deleted] bit NOT NULL,\r\n    [Created] datetime2 NOT NULL,\r\n    [RowVersion] rowversion NOT NULL,\r\n    CONSTRAINT [PK_Signups] PRIMARY KEY ([Id]),\r\n    CONSTRAINT [FK_Signups_AppUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AppUsers] ([Id]) ON DELETE CASCADE,\r\n    CONSTRAINT [FK_Signups_Events_EventId] FOREIGN KEY ([EventId]) REFERENCES [Events] ([Id]) ON DELETE CASCADE\r\n);",
    "{OriginalFormat}": "Failed executing DbCommand ({elapsed}ms) [Parameters=[{parameters}], CommandType=\u0027{commandType}\u0027, CommandTimeout=\u0027{commandTimeout}\u0027]{newLine}{commandText}"
  },
  "Scopes": []
}
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Signups_Events_EventId' on table 'Signups' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:e4518a03-b5e4-4bb1-b69b-29cab3418f83
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Signups_Events_EventId' on table 'Signups' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
778 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Wenbin Geng 736 Reputation points Microsoft Vendor
    2023-07-07T09:35:02.2766667+00:00

    Hi @David Thielen , Welcome to Microsoft Q&A.

    1. How does Entity Framework Core know the 1:many relationship between AppUser.Events and Event.Owner?

    Entity Framework Core has an important feature called Relationship navigations. In your example, the AppUser entity has the typical Collection navigations feature. EF Core can infer relationships based on the types and names of navigation properties. So Entity Framework Core knows about the one-to-many relationship between AppUser.Events and Event.Owner .

    If you have multiple properties of the same type, Entity Framework Core can still infer the relationship through the entity based on the Relationship navigations. However, it is not ruled out that EF Core infers ambiguity. At this time, it can be clarified through Configuring navigations its relationship.

     

    2. Will the deletion of AppUser records fail?

    First we need to figure out DeleteBehavior Enum

    Restrict: For context-tracked entities, foreign key property values in dependent entities will be set to null when the related principal is deleted. This helps keep the entity graph in a consistent state when tracking entities so that a fully consistent graph can be written to the database. If a property cannot be set to null because it is not a nullable type, an exception will be thrown when SaveChanges() is called.

    When the AppUser is deleted, the associated Events will not be automatically deleted due to the use of DeleteBehavior.Restrict. This means that if an AppUser is deleted, any events that refer to that AppUser as its owner will still have full foreign key constraints. But suppose that when you have an Event instance and access its Owner property, you will get the associated AppUser entity representing the owner of the event. That is, deleting an AppUser will fail if there is an associated event that references the AppUser.

    Best Regards,

    Wenbin


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.