CODEWITHSUNDEEP
asp.net-mvcentity-frameworkef-code-first
John Mathison
John Mathison

Reputation: 914

EF Code First Cascade NULL

We have ASP MVC Entity Framework 6 Code First project with 70+ tables in a MS SQL database.

For each record on each table we are storing the user that has modified it the last. The structure of almost every table is like this:

public class TableA
{
    public int Id{ get; set; }
    .....
    public DateTime? DateModified { get; set; }
    public int? UserModifiedId { get; set; }
    public virtual ApplicationUser UserModified { get; set; }
}

Our problem is we are getting an error when deleting a user if the user Id is in any of the tables' property UserModifiedId.

We need EF to set to NULL the UserModifiedId of all the tables where UserModifiedId = UserId.

In the past we setup this adding to the tables ON DELETE SET NULL, but EF doesn't allow to setup the tables like that.

Any idea how can we achieve this?

UPDATED

We already know EF manages this is the children are loaded into the context, but we can't load more than 70+ tables every time we want to delete a user.

Upvotes: 1

Views: 337

Answers (1)

ChW
ChW

Reputation: 3348

One approach could be to add a migration that modifies all your tables that should have CASCADE ON DELTE SET NULL like that way (only for your example code above):

public partial class AddOnDeleteSetNull : DbMigration
{
    public override void Up()
    {
        Sql("ALTER TABLE dbo.TableA DROP CONSTRAINT [FK_dbo.TableA_dbo.Users_UserModifiedId]");
        Sql("ALTER TABLE dbo.TableA ADD CONSTRAINT [FK_dbo.TableA_dbo.Users_UserModifiedId_SetNullOnDelete] FOREIGN KEY (UserModifiedId) REFERENCES dbo.Users(UserId) ON UPDATE NO ACTION ON DELETE SET NULL");
    }

    public override void Down()
    {
        // Here you have to undo the changes!
        // ...
    }
}

Upvotes: 1

Related Questions