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

Start by reading the Npgsql annotation provider and migrations SQL generator, especially their foreign-key constraint handling; the issue’s prototype shows the intended integration points but does not name repository file paths or tests. Check existing tests for foreign-key migration SQL and annotation handling, then add coverage for the proposed property- and column-based APIs. Done means generated migrations include the selected column list for SET NULL or SET DEFAULT.

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(")");
        }
    }
}