Validation to check if the record is being used before deleting

Question

The MVC of my project is stuccoed in services, controllers, Views and models.

My deletion screen works, but when the record is used in other tables it displays this error.

The DELETE statement conflicted with the REFERENCE constraint "FK_XYZ". The conflict occurred in database "ETEST", table "dbo.TEST", column 'ID_TEST'.
The statement has been terminated.

This is because the table’s PK is FK in other tables.

I would like that before deleting, the system would make a check if the record is used in the bank. To show a message to the friendly user before trying to delete. Ex.: Registration cannot be deleted because it is in use.

How do I build this validation?

Model

public int Id {get; set;}

[Display(Name = "Codigo", ResourceType = typeof(Resources.TestResources))]
[Required]
public string Codigo { get; set; }

[Display(Name = "Descricao", ResourceType = typeof(Resources.TestResources))]
[Required]
public string Descricao { get; set; }

[Display(Name = "UsuarioAnalistaCusto", ResourceType = typeof(Resources.TestResources))]
[Required]
public string UsuarioAnalistaCusto { get; set; }

Controller

public void Excluir(int id)
    {
        this.Service.Delete(id);
    }

Services

public void Delete(int id)
        {
            var item = this.context.Test.Find(id);
            this.context.Test.Remove(item);
            base.Save();
        }

Context

namespace TXT.Test.eTest.DataAccess
{
    public partial class EContext : DbContext
    {
        static EContext()
        {
            Database.SetInitializer<ECContext>(null);
        }

        public EContext(bool proxyCreationEnabled = true)
            : base("Name=EContext")
        {
            base.Database.CommandTimeout = 60 * 5; // TOOO: utilizar configuration
            base.Configuration.ProxyCreationEnabled = proxyCreationEnabled;

            base.Configuration.AutoDetectChangesEnabled = false;
        }

        public DbSet<Empresa> Empresas { get; set; }
        public DbSet<Fornecedor> Fornecedores { get; set; }
        public DbSet<Desenho> Desenhos { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new EMPRESAMap());
            modelBuilder.Configurations.Add(new FORNECEDORMap());
            modelBuilder.Configurations.Add(new DESENHOMap());
            ...
        }
    ...
    }
}

Answer 1

Your system actually uses Entity Framework yes. The correct way to warn the user that the registry cannot be deleted is something like:

var registro = context.Registros.Include(r => r.RegistroDependente).FirstOfDefault(/* Coloque aqui a condição para selecionar */);
if (registro.RegistroDependente != null) {
    ModelState.AddModelError("", "Este registro não pode ser apagado pelo motivo tal.");
    return View();
}

Answer 2

Taken from: SQL Server: how to know if any Row is referencing the Row to delete

If your system is multi user, which is probably, verify if the record can be deleted can cause a critical racing problem:

Imagine that the system determines that the record can be deleted, and allows the user to do so (or even proceed immediately to delete).

Imagine that between the two operations another user creates a related record (much less likely if they don’t pass by the user between confirming that it can be deleted and deleting, but still not impossible): exactly the error occurs that you tried to avoid, and that, by checking before, you consider that it could not occur, and therefore probably does not treat.

---

Thus, the proper solution is simpler than what you asked: try to do the operation, and treat the error. If the external key error is released, treat it and return a message to the user.

Answer 3

However, I agree with Rsinohara, it would be much more to put a Try/catch inside the service delete, in the example below it works in SQL Server:

public void Delete(int id)
{
      try
      {
         var item = this.context.Test.Find(id);
         this.context.Test.Remove(item);
         base.Save();
      }
      catch (DbUpdateException ex)
      {
         var sqlException = ex.GetBaseException() as SqlException;

         if (sqlException != null)
         {
             var numero = sqlException.Number;

             if (numero == 547)
             {
               throw ex.Message = "Existe outras entidade relacionadas a essa, não foi possível apagar.";
             }
         }
     }
}

Controller vc could use another Try/catch to get the launch exception message from the bottom layer and put in Modelstate.Addmodelerror as Gypsy did.