EF Core: OnDelete DeleteBehavior ClientSetNull Vs. SetNull in an Optional Relationship

In this post, let's have a look at the difference between DeleteBehavior.ClientSetNull and DeleteBehavior.SetNull when configuring OnDelete action in an Optional EF Core Relationship.  I am finding this important because sometimes it can confuse people including myself. 

Note: this post considers Microsoft SQL Server as the database provider.

So what's an Optional Relationship? Let's consider the following example.

public class Category
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public List<ToDo> ToDos { get; set; }
}
 
public class ToDo
{
    public int Id { get; set; }
 
    public string Title { get; set; }
 
    public int? CategoryId { get; set; }
 
    public Category Category { get; set; }
}

Here I have a simple ToDo context, I have ToDos and a ToDo can optionally have a Category. And for a given Category, I can find all ToDos under that particular Category. Now our scenario is, what needs to happen if someone Deletes a Category? Here Category entity is the Principal/Parent and ToDo entity is the Dependent/Child.

We can configure the OnDelete behavior on Principal/Parent as follows.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>()
        .HasMany(x => x.ToDos)
        .WithOne(x => x.Category)
        .HasForeignKey(x => x.CategoryId)
        .OnDelete(/* DeleteBehavior */);
}

Here since the Relationship is optional, we have the option not to delete Dependents/Children while deleting a Principal/Parent or on severing the relationship from the Principal/Parent (we can severe the relationship by clearing out all the Dependents/Children from the Principal/Parent or by setting the Principal/Parent navigation property to null in each Dependent/Child).

The Default OnDelete behavior for Optional Relationships is DeleteBehavior.ClientSetNull.

First, let's see how the Dependent/Child table is created when we configure the OnDelete behavior with DeleteBehavior.ClientSetNull and DeleteBehavior.SetNull. 

DeleteBehavior.ClientSetNull

CREATE TABLE [ToDos] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NULL,
    [CategoryId] int NULL,
    CONSTRAINT [PK_ToDos] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_ToDos_Categories_CategoryId] FOREIGN KEY ([CategoryId]) REFERENCES [Categories] ([Id])
);

DeleteBehavior.SetNull

CREATE TABLE [ToDos] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NULL,
    [CategoryId] int NULL,
    CONSTRAINT [PK_ToDos] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_ToDos_Categories_CategoryId] FOREIGN KEY ([CategoryId]) REFERENCES [Categories] ([Id]) ON DELETE SET NULL
);

Here you can see, that there is an important difference when DeleteBehavior.SetNull is used, that is from the database side ON DELETE behavior is configured.

Now let's attempt to delete the Principal/Parent, in this context, it's a Category.

Category category = await context.Categories
    .FirstOrDefaultAsync();

context.Categories.Remove(category);
await context.SaveChangesAsync();

The query being generated is as follows.

--Executed DbCommand (6ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;

DELETE FROM [Categories]
OUTPUT 1
WHERE [Id] = @p0;

When DeleteBehavior.ClientSetNull:

Here we are going to get the SqlException which makes perfect sense. If you have a look at how the Dependent/Child table got created above. when DeleteBehavior.ClientSetNull, from the database side, there was no ON DELETE behavior configured on the Foreign Key. So from the database side, it's not happy, because there are Dependents/Children associated with the Principal/Parent getting deleted and the database doesn't know what to do with them.

When DeleteBehavior.SetNull:

Here, the Delete operation will proceed without any errors, because the Foreign Key in Dependent/Child table is configured with ON DELETE SET NULL and the database will take care of the associated Dependents/Children by setting the FK to null.

Now in order to have a successful Delete on Principal/Parent when DeleteBehavior.ClientSetNull, we need to explicitly load the Dependents/Children to the DbContext. 

Category category = await context.Categories
    .Include(x => x.ToDos)
    .FirstOrDefaultAsync();

context.Categories.Remove(category);
await context.SaveChangesAsync();

Now this will cause EF Core to issue the following query when deleting the Principal/Parent.

--Executed DbCommand (4ms) [Parameters=[@p1='1', @p0=NULL (DbType = Int32), @p2='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;

UPDATE [ToDos] SET [CategoryId] = @p0
OUTPUT 1
WHERE [Id] = @p1;

DELETE FROM [Categories]
OUTPUT 1
WHERE [Id] = @p2;

Now EF Core will first UPDATE Dependents/Children setting the Foreign Key to NULL and then DELETE the Principal/Parent. So now, the Delete operation will succeed.

Hope this helps.

More read:
   Cascade Delete

Happy Coding.

Regards,
Jaliya