Model Builder: Avoid foreign key cycles

[HappyNomad]

The issue can be reproduced with this version:

"Microsoft.EntityFrameworkCore.SqlServer": "1.1.0-preview2-*",

and this context:

public class BloggingContext : DbContext
{
	public BloggingContext( DbContextOptions<BloggingContext> options )
		: base( options ) { }

	public DbSet<L> Ls { get; set; }
	public DbSet<AL> ZAs { get; set; }
}

and this model:

public class AL
{
	public int ID { get; set; }
	public PBase L { get; set; }
}

public class L
{
	public int ID { get; set; }
	public IList<T> Ts { get; set; }
}

public class T : P
{
	public P Pre { get; set; }
}

public class P : PBase
{
	public int PreID { get; set; }
}

public abstract class PBase
{
	public int ID { get; set; }
	public string Stuff { get; set; }
}

The exception is:

System.Data.SqlClient.SqlException was unhandled by user code
Class=16
HResult=-2146232060
LineNumber=1
Message=Introducing FOREIGN KEY constraint 'FK_PBase_PBase_PreID' on table 'PBase' 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.
Number=1785
Procedure=""
Server=(localdb)\mssqllocaldb
Source=Core .Net SqlClient Data Provider
State=0
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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(TaskCompletionSource1 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, IReadOnlyDictionary2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()
at EFGetStarted.AspNetCore.NewDb.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, BloggingContext dataContext) in C:\Users\Adrian\Development\Examples\ASP.NET Core\EFGetStarted.AspNetCore.NewDb\src\EFGetStarted.AspNetCore.NewDb\Startup.cs:line 56
InnerException:

[ajcvickers]

This is a SQL Server limitation in how it handles multiple cascade relationships. We don't try to compensate for this automatically because a) not all databases have this limitation and b) there are two main ways to deal with it and the choice of which to do should be made explicitly:

• The relationships can be changed in the EF model such that the pseudo-cycles don't exist, either my making the relationships optional so they don't get cascade delete by default, or by leaving them required but explicitly turning off cascade delete.
• The migration can be edited to remove the cascade delete in the database while leaving it in the model. This means that cascading behavior will still happen so long as all related entities are loaded into the context.

[HappyNomad]

Thanks for the info. This is an issue in two class hierarchies in my domain model. I plan to map both as TPT (#2266) or TPC (#3170) once the alternative is available. Am I correct that, in the above example, this issue will disappear when I move class PBase and its derived classes to an alternative mapping strategy?

If "yes" then I'd conjecture that the correlation between this issue and alternative mapping strategies isn't limited to the above example or my domain model. The larger the class hierarchy, the more likely those derived classes reference each other; also the more reason to use an alternative mapping strategy. At least those seem true in my case.

The alternative mapping strategies are on the back burner, so I'm stuck with one of the hacks suggested above for now. For short-term development needs, I can turn off the cascade delete behavior. As I implement more functionality, however, accommodating this workaround will become more cumbersome.

[ajcvickers]

@HappyNomad Maybe file an issue on SQL Server to be less restrictive in what they report as a "cycle"?

[HappyNomad]

@ajcvickers The issue was already filed: Foreign key that references the same table. It's "Active" but it's been around since 2009. Since adding TPT to EF-Core would solve the issue in my case, I hope it doesn't stagnate too.

Triggers are mentioned as another workaround. Could this be incorporated into EF-Core's SQL Server provider? At least the hacky stuff would be taken out of user code.

[rowanmiller]

We discussed this but decided we don't want to work around this limitation in the EF stack.

[Poltuu]

It feels to me like this SQL Server specificity could be handled similarly to others in SqlServerModelBuilderExtensions