Databases
StackOverflow2013
Postsdbo
PONavigation properties and cascade on delete
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONavigation properties and cascade on delete
    primarykey
    data
    text
    <p>I am using EF5 fluent-api to try to set up relationships/constraints between several tables, and I want these relationships to include cascade on delete and I think I'm missing something simple because what I've tried below, produces said error. Long post, but 99% code, not to complicated, but below mentioned error is received when trying to reinitialize my model - there is some constraint that is expected, but not found. Really scratching my head over this...any direction would be most appreciated.</p> <pre><code>namespace Deals.Core.DataAccess.Entities { public abstract class Entity&lt;TEntity&gt; : IEntity&lt;TEntity&gt; where TEntity : Entity&lt;TEntity&gt;, new() { private bool isEmpty; protected Entity() { this.isEmpty = false; } public static TEntity Empty { get { return new TEntity() { IsEmpty = true }; } } public bool Active { get; set; } public bool Deleted { get; set; } [NotMapped] public bool IsEmpty { get { return this.isEmpty; } protected set { this.isEmpty = value; } } public int Version { get; set; } } public class Site : Entity&lt;Site&gt;, ISite { public Guid Id { get; set; } public virtual ICollection&lt;User&gt; Users { get; set; } public virtual Survey Survey { get; set; } } public class Survey : Entity&lt;Survey&gt;, ISurvey { public Guid Id { get; set; } public virtual Site Site { get; set; } } public class User : Entity&lt;User&gt;, IUser { public Guid Id { get; set; } public virtual UserProfile UserProfile { get; set; } public Guid SiteId { get; set; } public virtual Site Site { get; set; } } public class UserProfile : Entity&lt;UserProfile&gt;, IUserProfile { public Guid Id { get; set; } public virtual User User { get; set; } } } namespace Deals.Core.DataAccess.Models { public class Context : DbContext, IContext { public DbSet&lt;Site&gt; Sites { get; set; } public DbSet&lt;Survey&gt; Surveys { get; set; } public DbSet&lt;User&gt; Users { get; set; } public DbSet&lt;UserProfile&gt; UserProfiles { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { this.MapSite(modelBuilder); this.MapSurvey(modelBuilder); this.MapUser(modelBuilder); this.MapUserProfile(modelBuilder); Database.SetInitializer(new MigrateDatabaseToLatestVersion&lt;Context, ContextConfiguration&gt;()); base.OnModelCreating(modelBuilder); } protected virtual void MapSite(DbModelBuilder modelBuilder) { // Ignore the IsEmpty property. this.MapEntity&lt;Site&gt;(modelBuilder); modelBuilder.Entity&lt;Site&gt;().HasKey(p =&gt; p.Id); modelBuilder.Entity&lt;Site&gt;().HasOptional(p =&gt; p.Survey); modelBuilder.Entity&lt;Site&gt;().HasOptional(p =&gt; p.Users); } protected virtual void MapUser(DbModelBuilder modelBuilder) { // Ignore the IsEmpty property. this.MapEntity&lt;User&gt;(modelBuilder); modelBuilder.Entity&lt;User&gt;().HasKey(p =&gt; p.Id); modelBuilder.Entity&lt;User&gt;().HasRequired(p =&gt; p.Site).WithMany(p =&gt; p.Users).HasForeignKey(p =&gt; p.SiteId); } protected virtual void MapUserProfile(DbModelBuilder modelBuilder) { // Ignore the IsEmpty property. this.MapEntity&lt;UserProfile&gt;(modelBuilder); modelBuilder.Entity&lt;UserProfile&gt;().HasKey(p =&gt; p.Id); // Why does adding .WillCascadeOnDelete() look for a user_id constraint on UserProfile that does not exist? //modelBuilder.Entity&lt;UserProfile&gt;().HasRequired(p =&gt; p.User).WithRequiredPrincipal(user =&gt; user.UserProfile); //// .WillCascadeOnDelete(); modelBuilder.Entity&lt;UserProfile&gt;().HasRequired(p =&gt; p.User); } protected virtual void MapSurvey(DbModelBuilder modelBuilder) { // Ignore the IsEmpty property. this.MapEntity&lt;Survey&gt;(modelBuilder); modelBuilder.Entity&lt;Survey&gt;().HasKey(p =&gt; p.Id); modelBuilder.Entity&lt;Survey&gt;().HasRequired(p =&gt; p.Site); //modelBuilder.Entity&lt;Site&gt;().HasOptional(p =&gt; p.Survey).WithOptionalPrincipal().WillCascadeOnDelete(); modelBuilder.Entity&lt;Survey&gt;().Property(p =&gt; p.SurveyXml).HasColumnType("xml").IsRequired(); } #region Generic Mapping protected virtual void MapEntity&lt;T&gt;(DbModelBuilder modelBuilder) where T : Entity&lt;T&gt;, new() { // Ignore the IsEmpty property. modelBuilder.Entity&lt;T&gt;() .Ignore(p =&gt; p.IsEmpty); } #endregion Generic Mapping } } </code></pre> <p>SQL generated:</p> <pre><code>create table [dbo].[Sites] ( [Id] [uniqueidentifier] not null, [Url] [nvarchar](max) null, [Description] [nvarchar](max) null, [Active] [bit] not null, [Deleted] [bit] not null, [Version] [int] not null, primary key ([Id]) ); create table [dbo].[Surveys] ( [Id] [uniqueidentifier] not null, [SurveyXml] [xml] not null, [Active] [bit] not null, [Deleted] [bit] not null, [Version] [int] not null, primary key ([Id]) ); create table [dbo].[Users] ( [Id] [uniqueidentifier] not null, [UserName] [nvarchar](max) null, [Password] [nvarchar](max) null, [LastLogin] [datetime] not null, [SiteId] [uniqueidentifier] not null, [Active] [bit] not null, [Deleted] [bit] not null, [Version] [int] not null, primary key ([Id]) ); create table [dbo].[UserProfiles] ( [Id] [uniqueidentifier] not null, [FirstName] [nvarchar](max) null, [LastName] [nvarchar](max) null, [MiddleInitial] [nvarchar](max) null, [Honorific] [nvarchar](max) null, [Email] [nvarchar](max) null, [Active] [bit] not null, [Deleted] [bit] not null, [Version] [int] not null, primary key ([Id]) ); </code></pre> <p>Сan't get "on delete cascade" here:</p> <pre><code>alter table [dbo].[Surveys] add constraint [Site_Survey] foreign key ([Id]) references [dbo].[Sites]([Id]); </code></pre> <p>This is good:</p> <pre><code>alter table [dbo].[Users] add constraint [User_Site] foreign key ([SiteId]) references [dbo].[Sites]([Id]) on delete cascade; </code></pre> <p>Can't get "on delete cascade" here:</p> <pre><code>alter table [dbo].[UserProfiles] add constraint [UserProfile_User] foreign key ([Id]) references [dbo].[Users]([Id]); </code></pre> <p>If I do this:</p> <pre><code> modelBuilder.Entity&lt;UserProfile&gt;() .HasRequired(p =&gt; // p.User) .WithRequiredPrincipal(user =&gt; user.UserProfile) .WillCascadeOnDelete(); // Instead of this: modelBuilder.Entity&lt;UserProfile&gt;().HasRequired(p =&gt; p.User); // The sql that is generated looks correct: alter table [dbo].[Users] add constraint [UserProfile_User] foreign key ([Id]) references [dbo].[UserProfiles]([Id]) on delete cascade; </code></pre> <p>However, I get this error when trying reinitialize my model when running tests; what's up with FK_dbo.UserProfiles_dbo.Users_Id?</p> <pre><code>Test Name: SiteRepository_Remove_TestPasses Test FullName: Deals.Core.Tests.Deals.Core.DataLibrary.Tests.Integration.SiteRepositoryIntegrationTests.SiteRepository_Remove_TestPasses Test Source: c:\Dev\Deals\Deals.Core.Tests\Deals.Core.DataLibrary.Tests\Integration\SiteRepository.Integration.Tests.cs : line 51 Test Outcome: Failed Test Duration: 0:00:01.4034458 Result Message: Initialization method Deals.Core.Tests.Deals.Core.DataLibrary.Tests.Integration.SiteRepositoryIntegrationTests.TestInitialize threw exception. System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: 'FK_dbo.UserProfiles_dbo.Users_Id' is not a constraint. Could not drop constraint. See previous errors.. Result StackTrace: 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&amp; dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.MigrateDatabaseToLatestVersion`2.InitializeDatabase(TContext context) at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() at Deals.Core.DataAccess.UnitOfWorkCore.ForceDatabaseInititialization() in c:\Dev\Deals\Deals.Core.DataAccess\UnitOfWorkCore.cs:line 164 at Deals.Core.Tests.Deals.Core.DataLibrary.Tests.Integration.SiteRepositoryIntegrationTests.TestInitialize() in c:\Dev\Deals\Deals.Core.Tests\Deals.Core.DataLibrary.Tests\Integration\SiteRepository.Integration.Tests.cs:line 28 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USabatishchev
    1. USgangelo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONavigation properties and cascade on delete
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PONavigation properties and cascade on delete
      1. USgangelo
      1. VTFavorite
    3. VO
      singulars
      1. PONavigation properties and cascade on delete
      1. This table or related slice is empty.
      1. VTUpMod
    1. COReally, really :( Can't you cook this down to the relevant parts for your question? I can't imagine that properties like `Description`, etc. etc. and all summary coments above the properties play any role for your question. Scrolling down through the code to find the important pieces is awful. I have stopped to find them to be honest.
      singulars
      1. PONavigation properties and cascade on delete
      1. USSlauma
    2. CO@Slauma - you serious? It's not that much code, besides, one person complains there is too much, another you didn't include enough.
      singulars
      1. PONavigation properties and cascade on delete
      1. USgangelo
    3. COFor me this mix of c#, comments, sql, exception messages, irrelevant pieces etc. all in one long section is awful. Nevermind, someone might have the nerves to go through it...
      singulars
      1. PONavigation properties and cascade on delete
      1. USSlauma
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload