CODEWITHSUNDEEP
sql-serverasp.net-mvcentity-framework
Toddams
Toddams

Reputation: 1499

Proper cascade delete

There are 3 entities:

User has ICollection<Event> and ICollection<Review>.

Event has virtual User navigation property, and ICollection<Review>

Structure of Review class

    public int Id { get; set; }

    public string Text { get; set; }

    public int Mark { get; set; }

    public ReviewStatus ReviewStatus { get; set; }

    public int EventId { get; set; }
    public virtual Event Event { get; set; }

    public int UserId { get; set; }
    public virtual AppUser User { get; set; }

And i got - Introducing FOREIGN KEY 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. See previous errors.

I can solve it by adding this on ModelCreating method of DbContext:

        modelBuilder.Entity<Event>().
            HasMany(e => e.Reviews).
            WithRequired(e => e.Event).
            WillCascadeOnDelete(false);

        modelBuilder.Entity<AppUser>().
            HasMany(u => u.Reviews).
            WithRequired(r => r.User).
            WillCascadeOnDelete(false);

But after this I can't delete Event or user from database, because foreign key error occurs. So is there another solution?

Upvotes: 1

Views: 127

Answers (1)

Arman Hamid Mosalla
Arman Hamid Mosalla

Reputation: 3355

You should decide between not being able to delete or inserting null in places of foreign-key when you delete, you can let the User to be deleted and insert null for any deleted Event that was foreignkey in Review and have orphaned Review, here is the code for making Event to Review relationship nullable:

modelBuilder.Entity<Review>()
           .HasOptional(t => t.Event)
           .WithMany(t => t.Reviews)
           .HasForeignKey(d => d.EventId)
           .WillCascadeOnDelete(false);


           modelBuilder.Entity<Review>()
            .HasOptional(p => p.Event)
            .WithMany(p => p.Reviews)
            .HasForeignKey(p => p.EventId);

also remember to lazy load the Event and Review with Include when deleting the user otherwise null will not get inserted, other point to make is that remember when you face situations like this when User have many Events and Events have many Reviews you have to do the null insertion for Event to Review yourself, EF doesn't do this for you, something like blow example:

var eventItems = _db.Events.Include(p => p.Reviews).(s => s.UserIdfk == UserId);

                foreach (var item in eventItems)
                {
                    _db.Events.Remove(item);
                }

I hope I've been able to convey the general approach.

Upvotes: 1

Related Questions