How to update database/model to allow deletion of a row that will result in the foreign key of another item becoming null

Question

My application has a class called Chemical with a primary key called ChemicalId. It also has a class SampleLocation that has a foreign key ChemicalId that is tied to the Chemical table.

I need to be able to remove a Chemical from the database (delete it) and thereby set the value of ChemicalId in each SampleLocation to null. However, when I attempt to delete the Chemical in my application, I get the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.SampleLocations_dbo.Chemicals_ChemicalId". The conflict occurred in database "aspnet-LIMS-20160112103244", table "dbo.SampleLocations", column 'ChemicalId'.

In my database, a SampleLocation does not 'need' a chemical assigned to it, so nulls are ok, but if there is an id in the ChemicalId column, then it MUST refer to a valid Chemical.

The column type of ChemicalId is of int? so nulls are allowed. My class does not have the [required] attribute on it, so why can I not do this delete?

After searching online for the last two days, I have some inkling that I need to do something with an OnModelCreating method, but I have no such method in my application anywhere and every example of one I find has it in a class that inherits from a class called DbContext, but my app has no such class (which is a whole other line of inquiry/rabbit hole I should go down one day.) Any ideas?

My models:

1) SampleLocation class:

public class SampleLocation
{
    [Key]
    [Display(Name="Location ID")]
    public int LocationId { get; set; }

    [StringLength(35, ErrorMessage = "The Lease Name is limited to 35 characters")]
    [Index("ix_uniqueSP", 1, IsUnique = true)]
    [Display(Name = "Lease")]
    public string LeaseName { get; set; }

    [Required]
    [StringLength(35, ErrorMessage = "The Well Number/Name is limited to 35 characters")]
    [Index("ix_uniqueSP", 2, IsUnique = true)]
    [Display(Name = "Well Name")]
    public string WellNumber { get; set; }

    //Foreign key
    [Required]
    [Index("ix_uniqueSP", 3, IsUnique = true)]
    [Display(Name = "Customer")]
    public int ClientId { get; set; }
    [ForeignKey("ClientId")]
    public virtual Company Companies { get; set; }

    //Foreign key
    [Required]
    [Index("ix_uniqueSP", 4, IsUnique = true)]
    [Display(Name = "District")]
    public int DistrictId { get; set; }
    [ForeignKey("DistrictId")]
    public virtual District Districts { get; set; }

    [Display(Name = "Foreman")]
    public string ClientForeman { get; set; }

    [Display(Name = "Zone")]
    public string ClientZone { get; set; }

    [Display(Name = "Run")]
    public string ClientRun { get; set; }

    [Display(Name = "API Number")]
    [RegularExpression(@"^\d{14}|\d{2}-?\d{3}-?\d{5}-?\d{2}-?\d{2}$", ErrorMessage = "A valid 14 digit API number is required.")]
    public string APINumber { get; set; }

    [Display(Name = "Sample Point Name")]
    public string SamplePointName
    {
        get { return LeaseName + " " + WellNumber; }
    }

    //Foreign key
    [Display(Name = "Chemical")]
    public int? ChemicalId { get; set; }
    [ForeignKey("ChemicalId")]
    public virtual Chemical Chemicals { get; set; }
}

2) IdentityModels class:

public class ApplicationUser : IdentityUser
{
    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
    {
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        return userIdentity;
    }

    public static ApplicationUser Create()
    {
        return new ApplicationUser();
    }

    public DbSet<ApplicationUser> AspNetUsers { get; set; }
    public string FirstName { get; set;  }
    public string LastName { get; set; }
    public string InitialedName { get; set; }
    public bool HasChangedPassword { get; set; }

    //Foreign Key
    public int Employer { get; set; }
    [ForeignKey("Employer")]
    public virtual Company Companies { get; set; }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    public DbSet<LabSample> LabSamples { get; set; }
    public DbSet<SampleLocation> SampleLocations { get; set; }
    public DbSet<Company> Companies { get; set; }
    public DbSet<Chemical> Chemicals { get; set; }
    public DbSet<LegitDomain> Domains { get; set; }
    public DbSet<District> Districts { get; set; }
    public DbSet<AccountManagerAssignment> AccountManagerAssignments { get; set; }
}

3) Chemical class:

public class Chemical
{
    [Key]
    public int ChemicalId { get; set; }
    public string Name { get; set; }
    public double ProductFactor { get; set; }

    public virtual ICollection<SampleLocation> SampleLocations { get; set; }
}

Any help would be much appreciated.

Answer 1

The code that worked for me is below. Thanks again, Kevin.

    // POST: LabData/ChemicalDelete/5
    [Authorize(Roles = "canDeleteDbAdmin")]
    [HttpPost, ActionName("ChemicalDelete")]
    [ValidateAntiForgeryToken]
    public ActionResult DeleteChemicalConfirmed(int id)
    {
        foreach (SampleLocation l in db.SampleLocations)
        {
            if (l.ChemicalId == id)
            {
                l.ChemicalId = null;
                db.Entry(l).State = EntityState.Modified;
            }
        }
        db.SaveChanges();
        Chemical c = db.Chemicals.Find(id);
        db.Chemicals.Remove(c);
        db.SaveChanges();
        return RedirectToAction("ChemicalList");
    }

Answer 2

You are getting this error because when you are attempting to delete the record from your Chemical table, one or more records in the SampleLocation table still reference it.

You have to first change the values of all records in the SampleLocation table that reference the Chemical record that you wish to delete. Once that is done, and no more records in SampleLocation are referencing the record in the Chemical table, you should be able to delete it.