Entity Framework DELETE statement conflicted with the REFERENCE constraint

Question

I’m pretty new to EF and I have a little problem.

I just want to delete an item in my database. I’m using SQL Server 2012 Express, VS2012, AdventureWorks 2012.

The query that I execute is the following:

context = new AWEntities();
            var removedItem = context.Addresses
                .Include("StateProvince")
                .Include("SalesOrderHeaders")
                .Include("BusinessEntityAddresses").Single(d => d.AddressID == 11);
            context.Addresses.Remove(removedItem);

context.SaveChanges();

The error that I get is

The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeader_Address_ShipToAddressID". The conflict occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader", column 'ShipToAddressID'. The statement has been terminated.

Is this actually a good way to delete items and the according entries in the other tables?

Please point me into the right direction.

   public partial class Address
    {
        public Address()
        {
            this.BusinessEntityAddresses = new HashSet<BusinessEntityAddress>();
            this.SalesOrderHeaders = new HashSet<SalesOrderHeader>();
        }

        public int AddressID { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string City { get; set; }
        public int StateProvinceID { get; set; }
        public string PostalCode { get; set; }
        public System.Data.Spatial.DbGeography SpatialLocation { get; set; }
        public System.Guid rowguid { get; set; }
        public System.DateTime ModifiedDate { get; set; }

        public virtual StateProvince StateProvince { get; set; }
        public virtual ICollection<BusinessEntityAddress> BusinessEntityAddresses { get; set; }
        public virtual ICollection<SalesOrderHeader> SalesOrderHeaders { get; set; }
    }


public partial class StateProvince
    {
        public StateProvince()
        {
            this.Addresses = new HashSet<Address>();
            this.SalesTaxRates = new HashSet<SalesTaxRate>();
        }

        public int StateProvinceID { get; set; }
        public string StateProvinceCode { get; set; }
        public string CountryRegionCode { get; set; }
        public bool IsOnlyStateProvinceFlag { get; set; }
        public string Name { get; set; }
        public int TerritoryID { get; set; }
        public System.Guid rowguid { get; set; }
        public System.DateTime ModifiedDate { get; set; }

        public virtual ICollection<Address> Addresses { get; set; }
        public virtual CountryRegion CountryRegion { get; set; }
        public virtual ICollection<SalesTaxRate> SalesTaxRates { get; set; }
        public virtual SalesTerritory SalesTerritory { get; set; }
    }
}

public partial class BusinessEntityAddress
{
    public int BusinessEntityID { get; set; }
    public int AddressID { get; set; }
    public int AddressTypeID { get; set; }
    public System.Guid rowguid { get; set; }
    public System.DateTime ModifiedDate { get; set; }

    public virtual Address Address { get; set; }
    public virtual AddressType AddressType { get; set; }
    public virtual BusinessEntity BusinessEntity { get; set; }
}


public partial class SalesOrderHeader
    {
        public SalesOrderHeader()
        {
            this.SalesOrderDetails = new HashSet<SalesOrderDetail>();
            this.SalesOrderHeaderSalesReasons = new HashSet<SalesOrderHeaderSalesReason>();
        }

        public int SalesOrderID { get; set; }
        public byte RevisionNumber { get; set; }
        public System.DateTime OrderDate { get; set; }
        public System.DateTime DueDate { get; set; }
        public Nullable<System.DateTime> ShipDate { get; set; }
        public byte Status { get; set; }
        public bool OnlineOrderFlag { get; set; }
        public string SalesOrderNumber { get; set; }
        public string PurchaseOrderNumber { get; set; }
        public string AccountNumber { get; set; }
        public int CustomerID { get; set; }
        public Nullable<int> SalesPersonID { get; set; }
        public Nullable<int> TerritoryID { get; set; }
        public int BillToAddressID { get; set; }
        public int ShipToAddressID { get; set; }
        public int ShipMethodID { get; set; }
        public Nullable<int> CreditCardID { get; set; }
        public string CreditCardApprovalCode { get; set; }
        public Nullable<int> CurrencyRateID { get; set; }
        public decimal SubTotal { get; set; }
        public decimal TaxAmt { get; set; }
        public decimal Freight { get; set; }
        public decimal TotalDue { get; set; }
        public string Comment { get; set; }
        public System.Guid rowguid { get; set; }
        public System.DateTime ModifiedDate { get; set; }

        public virtual Address Address { get; set; }
        public virtual ShipMethod ShipMethod { get; set; }
        public virtual CreditCard CreditCard { get; set; }
        public virtual CurrencyRate CurrencyRate { get; set; }
        public virtual Customer Customer { get; set; }
        public virtual ICollection<SalesOrderDetail> SalesOrderDetails { get; set; }
        public virtual SalesPerson SalesPerson { get; set; }
        public virtual SalesTerritory SalesTerritory { get; set; }
        public virtual ICollection<SalesOrderHeaderSalesReason> SalesOrderHeaderSalesReasons { get; set; }
    }

score 23 · Accepted Answer · answered Aug 13 '13 at 15:01

23

Can't really tell much from what you have said, but you may benefit from looking into using the DbModelBuilder to solve cascade issues:

            modelBuilder.Entity<Parent>()
                .HasMany<Child>(c => c.Children)
                .WithOptional(x => x.Parent)
                .WillCascadeOnDelete(true);

Again, would need more information about your model structure to determine if this is the right approach.

Either that or in your delete method, remove any children first, and then remove the parent.

answered Aug 13 '13 at 15:01

Thewads

4,953
11
55
71

Thank you for your answer. I added code maybe this makes my problem clearer. Regards – user2675973 Aug 13 '13 at 18:27
For reference: https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbmodelbuilder – General Grievance Sep 03 '20 at 11:47
Also, this approach works for Code First (as opposed to Database/Model First) projects. For that, see [this answer](https://stackoverflow.com/a/48075110/4294399) – General Grievance Sep 03 '20 at 14:04

score 12 · Answer 2 · answered Jul 05 '17 at 06:27

12

  modelBuilder.Entity<Parent>()
  .HasMany<Child>(c => c.Children)
  .WithOptional(x => x.Parent)
  .WillCascadeOnDelete(true);

or use Include

  var adv = db.Adv.Include(b => b.Features)
                  .Include(b => b.AdvDetails)
                  .Include(b => b.AdvGallery)
                  .FirstOrDefault(b => b.Id == id);
  db.Adv.Remove(adv);

for .HasMany(...).WithMany(...) Include is ok

answered Jul 05 '17 at 06:27

Hossein Hajizadeh

1,357
19
10

2

Entity Framework should setup up your model builder correctly. I had a similar problem and simply including the added lists provided the information for entity framework to manage foreign key deletion. – Peter Suwara May 29 '18 at 15:03
1

That '.Include()' has bitten in the butt a few times now. For some reason my tries at the 'modelBuilder' route didn't work in this scenario. Maybe I had some slight discrepancy that was holding it back. Anyhow, the '.Include' route worked for me in this scenario. thx. – Evan Sevy Sep 10 '19 at 21:43

score 7 · Answer 3 · edited Jan 03 '18 at 10:14

7

You can resolve this issue on SQL side

Method 1 :

First, you need to find on which table this FK constraint has been defined, through using Replication monitor.
Right click on that FK, click Modify, you should get popup box like one shown below.

From the popup box, Select Cascade for del.

Method 2 :

set ON DELETE CASCADE in sql at the end of constraint.

edited Jan 03 '18 at 10:14

a1626

2,953
1
19
34

answered Jan 03 '18 at 10:02

Muhammad

71
1
1

2

While this works, If you're using Code-First on your project with CI, this isn't really a viable option. You want it configured to be automatic to eliminate this type of interaction. In that kind of situation, you might instead want to use the Fluent-API during OnModelCreated. – Kris Coleman Feb 17 '18 at 20:26
1

This approach works for Database/Model first projects. – General Grievance Sep 03 '20 at 14:00

score 3 · Answer 4 · answered May 23 '19 at 09:22

3

In EF Core the syntax in builder is as follows:

builder.HasOne(b => b.Parent )
  .WithMany(a => a.Children)
  .OnDelete(DeleteBehavior.Cascade);

https://learn.microsoft.com/en-us/ef/core/saving/cascade-delete

answered May 23 '19 at 09:22

Simopaa

449
5
8

score 0 · Answer 5 · answered Jul 06 '20 at 20:57

I got this error when I created Entity B, that referenced Entity A, and then tried to delete Entity A. SQL/EF did not allow me to leave that dangling Id reference, since the objcet no longer existed. Cascading deletes would solve this, but I wanted B to persist. So I have to remove the reference from B before deleting A:

    var existingContractApprovers = _repo.Query<ChangeOrderApproverForContract>().Where(coafc => coafc.ContractId == key).ToList();
    //remove refs to contract approvers to preserve data integrity
    foreach(var contractApp in existingContractApprovers)
    {
        var associatedChangeOrderApprovers = _repo.Query<ChangeOrderApprover>().AsNoTracking().Where(coafc => coafc.ChangeOrderApproverForContractId == contractApp.Id).ToList();
        foreach(var coApp in associatedChangeOrderApprovers)
    {
            _repo.Edit(coApp);
            coApp.ChangeOrderApproverForContractId = null;
        }
    }
    _repo.SaveChanges();

    //remove the contract approvers
    foreach (var contractApp in existingContractApprovers)
    {
        _repo.Delete(contractApp);
    }
    _repo.SaveChanges();

Qudus · Answer 6 · 2020-09-04T01:35:01.443

You can do this in EF Core.

modelBuilder.Entity<Parent>()
    .HasMany<Child>(c => c.Children)
    .WithOne(s => s.Parent)
    .OnDelete(DeleteBehavior.Cascade);

The safer alternative is to make sure all children are deleted before deleting the parent. You should cascade on delete only if you are completely aware of how your entities relate. For example, you could have lots of orders connected to a certain category in your e-commerce store. Once the category is deleted, all the orders and any entity whose foreign keys are connected to this parent category will be gone.

Entity Framework DELETE statement conflicted with the REFERENCE constraint

6 Answers6