ASP.NET MVC delete button

Question

When I click the delete button, I get this error.

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Reserva_Quarto". The conflict occurred in database "GestãoHotel", table "dbo.Reserva", column 'ID_Quarto'.

Does anyone know the reason?

Controller:

// GET: Quartos/Delete/5
public ActionResult Delete(int? id)
{
      if (id == null)
      {
          return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
      }

      Quarto quarto = db.Quarto.Find(id);

      if (quarto == null)
      {
           return HttpNotFound();
      }

      return View(quarto);
}

// POST: Quartos/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
     Quarto quarto = db.Quarto.Find(id);
     db.Quarto.Remove(quarto);
     db.SaveChanges();
     return RedirectToAction("Index");
}

View:

<div class="table-responsive panel">
 <table class="table">
  <tbody>
     <tr>
        <td class="text-success"><i class="fa fa-list-ol"></i> Nº Quarto</td>
        <td>@Model.ID_Quarto</td>
      </tr>
      <tr>
        <td class="text-success"><i class="fa fa-bed"></i> Tipo de Quarto</td>
        <td>@Model.TipoQuarto</td>
      </tr>
    </tbody>
   </table>
</div>

<table class="table">
 <tbody>
  <tr>
     @using (Html.BeginForm())
     {
     @Html.AntiForgeryToken()
     <td class="text-center">
      <button type="submit" class="btn btn-danger btn-circle btn-xl" data-toggle="tooltip" title="Eliminar"> <i class="glyphicon glyphicon-ok"></i></button>
 <button type="button" onclick="location.href='@Url.Action("Index", "Quartos")'" class="btn btn-primary btn-circle btn-xl" data-toggle="tooltip" title="Voltar"><i class="glyphicon glyphicon-arrow-left"></i></button>
     </td>
      }
    </tr>
   </tbody>
 </table>

Connection string:

<connectionStrings>
    <add name="Hotel" 
         connectionString="Data Source=DESKTOP-BC284NS\SQLEXPRESS;initial catalog=GestãoHotel;integrated security=True;" 
         providerName="System.Data.EntityClient" />
    <add name="HotelEntities" 
         connectionString="metadata=res://*/Models.BaseDados.GestãoHotel.csdl|res://*/Models.BaseDados.GestãoHotel.ssdl|res://*/Models.BaseDados.GestãoHotel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=DESKTOP-BC284NS\SQLEXPRESS;initial catalog=GestãoHotel;integrated security=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>

Answer 1

You have to delete Reservas first before deleting the Quarto as follows:

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
     List<Reserva> Reservas = db.Reserva.Where(r => r.ID_Quarto == id).ToList();
     db.Reserva.RemoveRange(Reservas);

     Quarto quarto = db.Quarto.Find(id);
     db.Quarto.Remove(quarto);
     db.SaveChanges();
     return RedirectToAction("Index");
}

Moreover for permanent solution, add the following configuration in DbContext then run a migration and update the database accordingly:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     base.OnModelCreating(modelBuilder);

     modelBuilder.Entity<Reserva>().HasRequired(j => j.Quarto)
                                   .WithMany(c => c.Reservas)
                                   .HasForeignKey(j => j.ID_Quarto).WillCascadeOnDelete(true);
}

Hope your problem will be solved!

Answer 2

I think you have options for this. - Option 1: Use ON DELETE CASCADE - Option 2: Deleting In Correct Order. It means that you should delete the rows from Reserva first, then the rows from Quarto.

You can follow this sample. Hope to help, my friend :))

//Option 1:

ALTER TABLE <child_table> WITH CHECK 
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE

---

OPTION 2:

// POST: Quartos/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            //Delete the rows from Reserva first
            var reservaObj = db.Reserva.Where(t => t.ID_Quarto == id).ToList();
            db.Reserva.RemoveRange(reservaObj);

            Quarto quarto = db.Quarto.Find(id);
            db.Quarto.Remove(quarto);
            db.SaveChanges();
            return RedirectToAction("Index");
        }