Cascade on Delete/Nullable Foreign Key

Question

I created a code first project in MVC 5 / EF 6 using an existing database. I have a one-to-many relationship between two tables. A record in the in "WebLeads" table can contain many records in the "Notes" table.

The problem is that when I created the data model, I didn't notice that the LeadID foreign key in the Notes table allowed nulls. Now when I try to delete a record in WebLeads, I am getting the following error (if there are related records in the Notes table).

    The DELETE statement conflicted with the REFERENCE constraint "FK_Notes_WebLead". The conflict occurred in database "databasename", table "dbo.Notes", column 'LeadID'.
    The statement has been terminated.

I tried adding the "Will Cascade on Delete", but it didn't work. From what I understand, cascade on delete will only work if the Foreign Key is not nullable.

     modelBuilder.Entity<WebLead>()
    .HasMany(e => e.Notes)
    .WithOptional(e => e.WebLead)
    .HasForeignKey(e => e.LeadID)
    .WillCascadeOnDelete(true);

So through SQL Management studio, I changed the FK LeadID to not allow nulls, and updated the code in the Notes model as follows:

   public int LeadID { get; set; } //removed the int?

When I try to build the project, the first query to the WebLeads table throws the following error:

    One or more validation errors were detected during model generation:

    Project.Models.WebLead_Notes: : Multiplicity conflicts with the referential constraint in Role 'WebLead_Notes_Source' in relationship 'WebLead_Notes'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

What else do I need to do in Code First in order to get the project to recognize the change in relationship between the WebLeads and the Notes table? Am I taking the correct approach?

Because each record in the Note table will require a LeadID, it seems the best thing to do is require the LeadID foreign key...but I seem to be missing a step or 2 in order to make this work. I am new to Code First design, so I am guessing my problem lies within the modelBuilder?

Thanks!

Notes Model

    namespace Project.Models

public partial class Note
{
    public int NoteID { get; set; }

    public int LeadID { get; set; }

    public string NoteText { get; set; }

    [StringLength(50)]
    public string NoteBy { get; set; }

    [Column(TypeName = "datetime2")]
    public DateTime? NoteDate { get; set; }

    public virtual WebLead WebLead { get; set; }
}

}

WebLeads Model

   namespace Project.Models
   {
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;

public partial class WebLead
{
    public WebLead()
    {
        Notes = new HashSet<Note>();
    }

    [Key]
    public int LeadID { get; set; }

    [Required]
    [StringLength(35)]
    [DisplayName("First Name")]
    public string FirstName { get; set; }

    [Required]
    [StringLength(50)]
    [DisplayName("Last Name")]
    public string LastName { get; set; }

    [Required]
    [StringLength(15)]        
    public string Phone { get; set; }

    [Required]
    [StringLength(75)]
    public string Email { get; set; }

    [Required]
    [StringLength(20)]
    public string County { get; set; }


    [Column(TypeName = "datetime2")]
    [DisplayName("Lead Date")]
    public DateTime? LeadDate { get; set; }

    [StringLength(35)]
    [DisplayName("Lead Status")]
    public string LeadStatus { get; set; }

    public virtual ICollection<Note> Notes { get; set; }
}

}

Since you are using code first, did you run update-database command after updating your model? — Felipe Gavilán, Apr 10 '15 at 02:03
BTW: You said "I created a code first project in MVC 5 / EF 6 using an existing database", then said that you are using Code first. Are sure you are not using database first? — Felipe Gavilán, Apr 10 '15 at 02:07
I created a Code first project modeled off an existing database. MVC % gives you that option when you are creating a new project. I do not have an EDMX file. — Daniela, Apr 10 '15 at 02:11
I enabled migrations, and made the change to the foreign key. I still see the following error: Project.Models.WebLead_Notes: : Multiplicity conflicts with the referential constraint in Role 'WebLead_Notes_Source' in relationship 'WebLead_Notes'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'. — Daniela, Apr 10 '15 at 02:19
What query is throwing that error? Is it and insert/update? It could be that you are not filling the value of LeadID in the Note model — Felipe Gavilán, Apr 10 '15 at 02:29
That error was occurring when I tried to update-database, also on my first query on build, which is just getting a list of record details from the WebLeads table — Daniela, Apr 10 '15 at 02:32

score 0 · Answer 1 · edited Apr 05 '18 at 10:56

0

I found my answer to this question using the link below. I had to individually delete the records in the notes table.

How to delete multiple records with Entity Framework ASP.Net MVC 5?

db.Notes.RemoveRange(db.Notes.Where(c => c.LeadID == id));

edited Apr 05 '18 at 10:56

croxy

4,082
9
28
46

answered Apr 11 '15 at 19:15

Daniela

657
1
10
25

Cascade on Delete/Nullable Foreign Key

1 Answers1