API Proposal: `HasOnDeleteSetNullProperties()` to support Postgres 15+ `ON DELETE SET NULL (column_a, column_b)` column lists

[crozone]

Preface

PostgreSQL 15+ added support for specifying a list of columns after a ON DELETE SET NULL or ON DELETE SET DEFAULT constraint, which specifies which subset of a composite foreign key's columns to set to NULL when the principal is deleted, instead of all columns in the foreign key.

The DDL constraints documentation states:

The actions SET NULL and SET DEFAULT can take a column list to specify which columns to set. Normally, all columns of the foreign-key constraint are set; setting only a subset is useful in some special cases.

For example:

FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id);

Npgsql does not yet support a built-in way to target specific SET NULL columns using the standard Fluent API.

Proposed API

An extension method for ReferenceCollectionBuilder named HasOnDeleteSetNullColumns() for specifying raw column names, and HasOnDeleteSetNullProperties() for the property names, with overloads for string array and lambda expression:

public static ReferenceCollectionBuilder HasOnDeleteSetNullColumns(
    this ReferenceCollectionBuilder builder,
    params string[] setNullColumnNames)

public static ReferenceCollectionBuilder HasOnDeleteSetNullProperties(
    this ReferenceCollectionBuilder builder,
    params string[] setNullPropertyNames)

public static ReferenceCollectionBuilder<TPrincipalEntity, TDependentEntity> HasOnDeleteSetNullProperties<TPrincipalEntity, TDependentEntity>(
    this ReferenceCollectionBuilder<TPrincipalEntity, TDependentEntity> builder,
    Expression<Func<TDependentEntity, object?>> setNullPropertiesExpression)
        where TPrincipalEntity : class
        where TDependentEntity : class

Example usage

modelBuilder.Entity<Author>()
    .HasMany(a => a.Posts)
    .WithOne(p => p.Author)
    .HasPrincipalKey(a => new { a.TenantId, a.Id })
    .HasForeignKey(p => new { p.TenantId, p.AuthorId })
    .OnDelete(DeleteBehavior.SetNull)
    .HasOnDeleteSetNullProperties(p => p.AuthorId);
    // or .HasOnDeleteSetNullProperties("AuthorId");
    // or .HasOnDeleteSetNullColumns("author_id");

Workarounds

Current workarounds include manually modifying the migrations with custom SQL to drop the initial constraint and re-add it with the column list, or to manually extend the migration generator. The latter being the path I took to prototype an implementation of this proposal, which may serve as the basis for a future PR.

This prototype implements:

• Two custom annotations:
  • "CustomNpgsql:OnDeleteSetNull:PropertyNames" is the property name list
  • "CustomNpgsql:OnDeleteSetNull:ColumnNames" is the actual column name list
• ReferenceCollectionBuilder extension methods
  • Adds the annotations to the Foreign Key Constraint
• Derived NpgsqlAnnotationProvider
  • Translates "CustomNpgsql:OnDeleteSetNull:PropertyNames" into concrete column names and re-adds them into the relational model as "CustomNpgsql:OnDeleteSetNull:ColumnNames"
  • Yields existing "CustomNpgsql:OnDeleteSetNull:ColumnNames" to the relational model
• Derived MigrationsSqlGenerator
  • Adds on the additional column list SQL during ForeignKeyConstraint generation

Extension methods

public static ReferenceCollectionBuilder HasOnDeleteSetNullColumns(
    this ReferenceCollectionBuilder builder,
    params string[] setNullColumnNames)
    {
        builder.HasAnnotation(
            "CustomNpgsql:OnDeleteSetNull:ColumnNames",
            setNullColumnNames);

        return builder;
    }

public static ReferenceCollectionBuilder HasOnDeleteSetNullProperties(
    this ReferenceCollectionBuilder builder,
    params string[] setNullPropertyNames)
    {
        builder.Metadata.SetAnnotation(
            "CustomNpgsql:OnDeleteSetNull:PropertyNames",
            setNullPropertyNames);

        return builder;
    }

public static ReferenceCollectionBuilder<TPrincipalEntity, TDependentEntity> HasOnDeleteSetNullProperties<TPrincipalEntity, TDependentEntity>(
    this ReferenceCollectionBuilder<TPrincipalEntity, TDependentEntity> builder,
    Expression<Func<TDependentEntity, object?>> setNullPropertiesExpression)
        where TPrincipalEntity : class
        where TDependentEntity : class
    {
        string[] setNullPropertyNames = setNullPropertiesExpression
            .GetMemberAccessList()
            .Select(member => member.Name)
            .ToArray();

        builder.Metadata.SetAnnotation(
            "CustomNpgsql:OnDeleteSetNull:PropertyNames",
            setNullPropertyNames);

        return builder;
    }

Custom NpgsqlAnnotationProvider

public class CustomNpgsqlAnnotationProvider : NpgsqlAnnotationProvider
{
    public CustomNpgsqlAnnotationProvider(RelationalAnnotationProviderDependencies dependencies)
        : base(dependencies)
    {
    }

    public override IEnumerable<IAnnotation> For(IForeignKeyConstraint foreignKeyConstraint, bool designTime)
    {
        foreach(IAnnotation baseAnnotion in base.For(foreignKeyConstraint, designTime)) {
            yield return baseAnnotion;
        }

        foreach (IForeignKey thisForeignKey in foreignKeyConstraint.MappedForeignKeys)
        {
            if (thisForeignKey.FindAnnotation("CustomNpgsql:OnDeleteSetNull:ColumnNames")?.Value is string[] columnNames)
            {
                yield return new Annotation(
                        "CustomNpgsql:OnDeleteSetNull:ColumnNames",
                        columnNames
                    );
            }

            // Translate CustomNpgsql:OnDeleteSetNull:PropertyNames -> CustomNpgsql:OnDeleteSetNull:ColumnNames
            if (thisForeignKey.FindAnnotation("CustomNpgsql:OnDeleteSetNull:PropertyNames")?.Value is string[] propertyNames)
            {
                // Translate property names to column names using the set of columns from the foreign key constraint
                string[] columnNames = propertyNames
                    .SelectMany(p =>
                        foreignKeyConstraint.Columns
                        .Where(c => c.PropertyMappings.Any(pm => pm.Property.Name == p))
                    )
                    .Select(c => c.Name)
                    .ToArray();

                yield return new Annotation(
                        "CustomNpgsql:OnDeleteSetNull:ColumnNames",
                        columnNames
                    );
            }
        }
    }
}

Custom NpgsqlMigrationsSqlGenerator

public class CustomNpgsqlMigrationsSqlGenerator : NpgsqlMigrationsSqlGenerator {
    protected override void ForeignKeyConstraint(AddForeignKeyOperation operation, IModel? model, MigrationCommandListBuilder builder)
    {
        // Write the constraint as standard. This is not yet terminated.
        base.ForeignKeyConstraint(operation, model, builder);

        // Only append column list to SET NULL or SET DEFAULT
        if (operation.OnDelete is ReferentialAction.SetNull or ReferentialAction.SetDefault
            && operation?.FindAnnotation("CustomNpgsql:OnDeleteSetNull:ColumnNames")?.Value is string[] columnNames && columnNames.Length > 0)
        {    
            // Append the column list
            builder.Append(" (");
            builder.Append(ColumnList(columnNames));
            builder.Append(")");
        }
    }
}

[roji]

Doing this fully would unfortnuately require more than just tacking on the necessary PG-specific DDL in migrations - EF's change tracking facility is aware of DeleteBehavior: when you delete something and DeleteBehavior.SetNull is set, it nulls out the appropriate properties in the tracked entities in memory. If we only apply this in migration DDL, we'd have a discrepancy between what actually happens in the database (only specific columns are nulled as per the feature) and what's tracked inside EF (all foreign key properties are nulled out, as the change tracker is unaware of the feature). Implementing support in the change tracker would require non-trivial upstream changes in EF itself.

Unfortunately we're sometimes limited when in comes to exposing PG-specific features in the EF provider because of complexity like this. The good news is that if you're OK with migration-only support (which is what you seem to be asking for above), you can easily do this yourself, either by using custom SQL in your migration to ALTER the foreign key, or via a custom MigrationsSqlGenerator as you've done above. But I don't think it would be appropriate to do something like this inside the provider itself while leaving the change tracker fully unaware.

[crozone]

@roji Thanks for your response, I was blissfully unaware that EF's change tracker did that internally. I hope that this only causes an in-memory discrepancy, and that EF is not going out of its way to explicitly UPDATE and null out the foreign key (and is just relying on the database to do it). I also assume that a .ReloadAsync() after every .SaveChangesAsync() would be a makeshift workaround, maybe a save changes interceptor would be viable for a DIY approach.

I guess this feature request can be parked until such a time that upstream EF supports the foundations for this feature.