EF Core DeleteBehavior.SetNull makes cyclic problems

Question

I am using EF Core 1.1.0 and I have a serious problem with cascading behaviour.

I have a model called Land like bellow:

public class Land
{
    public long Id { get; set; }
    public int HorizontalPosition { get; set; }
    public int VerticalPosition { get; set; }
    public bool IsPlaced { get; set; }

    // Relations
    public string UserId { get; set; }
    public virtual User User { get; set; }
    public long? BuildingId { get; set; }
    public virtual Building Building { get; set; }
}

and another model called Building like this:

public class Building
{
    public long Id { get; set; }
    public bool IsPermanent { get; set; }
    public int UpgradeCount { get; set; }

    // Relations
    public string UserId { get; set; }
    public virtual User User { get; set; }
    public int BuildingTypeId { get; set; }
    public virtual BuildingType BuildingType { get; set; }
    public virtual List<Land> Lands { get; set; }
}

and in dbcontext I have mentioned a relation as this:

...
modelBuilder.Entity<Land>()
            .HasOne(l => l.Building)
            .WithMany(b => b.Lands)
            .HasForeignKey(l => l.BuildingId)
            .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.SetNull);
...

but when I try to update the database I get this error:

System.Data.SqlClient.SqlException: Introducing FOREIGN KEY constraint 'FK_Lands_Buildings_BuildingId' on table 'Lands' 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 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.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 contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:65b8e08e-2d17-46ac-b734-385f88dd07b1
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Lands_Buildings_BuildingId' on table 'Lands' 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.

the generated migration is something like this:

...
migrationBuilder.AddForeignKey(
            name: "FK_Lands_Buildings_BuildingId",
            table: "Lands",
            column: "BuildingId",
            principalTable: "Buildings",
            principalColumn: "Id",
            onDelete: ReferentialAction.SetNull);
...

I have told that set the foreign key to null on delete but it wants me to do no action. Although in dbcontext there is no behavior called 'NoAction'!

Answer 1

In EF Core version 2.2.6 there is the ClientSetNull entry in the Microsoft.EntityFrameworkCore.DeleteBehavior enum.

Using ClientSetNull instead of SetNull made the difference in my case.

I don't understand why MsSqlServer isn't able to resolve multiple cascade paths, other engines are perfectly able to do so.