CODEWITHSUNDEEP
c#entity-framework
rukiman
rukiman

Reputation: 643

Entity Framework throwing conflict with reference constraint while deleting

I have the following entity declared

public class TransactionEvent
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public virtual List<TransactionSignInError> SignInErrors { get; set; }
}

And the context

public class TransactionAuditsDbContext : DbContext
{
    public virtual DbSet<TransactionEvent> TransactionEvents { get; set; }
}

Now when I try to delete a transaction event, I want the relevant SignInError rows to be deleted as well. I realize I can do this by using cascade on delete if I had set that up in the context, too late for that now.

How can I delete successfully a transaction? I'm getting this error.

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.TransactionSignInErrors_dbo.TransactionEvents_TransactionEvent_Id". The conflict occurred in database "db", table "dbo.TransactionSignInErrors", column 'TransactionEvent_Id'

I have tried clearing the SignInErrors list before deleting, that did get rid of the above error but left NULLs in the TransactionSignInErrors table.

Upvotes: 0

Views: 538

Answers (3)

atiyar
atiyar

Reputation: 8315

Your post has the tag of entity-framework. I'm not sure how things work with Entity Framework 6 or previous versions, but with Entity Framework Core you can solve your issue like -

var tEvent = dbCtx.TransactionEvents
            .Include(p=> p.SignInErrors)
            .FirstOrDefault(p => p.Id == id);
                
foreach (var error in eventx.SignInErrors)
{
    dbCtx.SignInErrors.Remove(error);
}

dbCtx.TransactionEvents.Remove(tEvent);
dbCtx.SaveChanges();

Upvotes: 0

Serge
Serge

Reputation: 43959

If you want to use a cascade delete you have to include the children:

var removingRow=_context.Set<TransactionEvent>()
.Include(x=> x.SignInErrors )
.Where(x => x.Id ==id)
.FirstOrDefault();

if(removingRow != null)
{
_context.Remove(removingRow);
_context.SaveChanges();
}

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30512

What you want, is "Cascade on Delete": if a TransactionEvent is deleted, then you also want that all its TransactionSignInErrors are deleted.

This works on a one-to-many relation, this does not work on a many-to-many-relation.

If you have a one-to-many relation between TransactionEvents and TransactionSignInErrors, and you followed the entity framework conventions, you will have classes like

public class TransactionEvent
{
    public Guid Id { get; set; }
    ...

    // Every TransactionEvent has zero or more TransactionSignInErrors (one-to-many)
    public virtual ICollection<TransactionSignInError> SignInErrors { get; set; }
}

public class TransactionSignInError
{
    public Guid Id { get; set; }
    ...

    // Every TransactionSignInError belongs to exactly oneTransactionEvent, using foreign key
    public Guid TransactionEventId {get; set;}
    public virtual TransactionEvent TransactionEvent { get; set; }
}

public class TransactionAuditsDbContext : DbContext
{
    public DbSet<TransactionEvent> TransactionEvents { get; set; }
    public DbSet<TransactionSignInError> TransactionSignInErrors {get; set;}
}

This is all that entity framework needs to know to detect the tables, the columns in the tables and the one-to-many relation between these two tables.

In entity framework the non virtual properties represent the columns in the table, the virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

The foreign key TransactionEventId is a real column, hence it is non-virtual. TransactionEvent is not a real column, it only refers to the relation, hence it is declared virtual.

If you stick to the conventions, there is no need for attributes, nor fluent API. Only if you want non-default identifiers for tables, columns, column types or non-default behaviour for table relations, you might need attributes or fluent API.

Default behaviour is cascade on delete: if you delete a TransactionEvent, all its TransactioinSigninErrors are also deleted.

I'm not sure whether your problems arise because you have a GUID as primary key, instead of an int. If you want, you can inform entity framework about your one-to-many relation and cascade on delete in OnModelCreating:

protected override void OnModelCreating (DbModelBuilder modelBuilder)
{
     // Every TransactionEvent has zero or more TransactionSignInErrors
     // Every TransactionSignInError belongs to exactly one TransactionEvent
     // using foreign key TransactionEventId.
     // Also: cascade on delete:
     modelBuilder.Entity<TransactionEvent>()
         .HasMany(transactionEvent => transactionEvent.TransactionSignInErrors)
         .WithRequired(transactionSignInError => transactionSignInError.TransactionEvent)
         .HasForeignKey(transactionSignInError => transactionSignInError.TransactionEventId)
         .WillCascadeOnDelete();

So three major changes to your code:

  • The DbSets in the DbContext are non-virtual
  • Added the table TransactionSignInErrors to your DbContext
  • If that is not enough for CascadeOnDelete (check this first!) add fluent API.

Small change: Use ICollection instead of IList.

Rationale: if you fetch a TransactionEvent with its TransactionSignInErrors, does TransactionEvent.SignInErrors[4] have a defined meaning? Wouldn't it be better if people have no access to methods that they don't know what they really mean?

Upvotes: 1

Related Questions