The operation failed: The relationship could not be changed because one or more of the foreign-key - EF Code first

simpledeveloper

I am using Entity Framework Code First - I am getting the following message in deleting any of the tables data - like CaseNov, ViolationsNov and ViolationTypeNov are three tables which are to handle many to many relationships between Case-Nov, Violation-Nov and ViolationType-Nov, I am getting error messages even if I trying to delete the detailed tables like: CaseNov, ViolationsNov, ViolationTypeNov or row directly from NOV table, I am getting the similar type of message - any help please? I am using Entity Framework Code First for deleting it.

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key
property is set to a null value. If the foreign-key does not support null values, a new relationship
must be defined, the foreign-key property must be assigned another non-null value or the unrelated
object must be deleted.

Here is the code for deleting records any help please:

    public bool Delete(NOV nov, bool performCommit = true)
    {
        System.Data.Entity.DbContextTransaction dbOperation = null;
        if (performCommit)
            dbOperation = UnitOfWork.BeginTransaction();
        try
        {               
            //deleting all OneToMany references to NOV                
            List novRels = UnitOfWork.ViolationTypeNOVRepository
                .GetAll().Where(x => x.NOVId == nov.NOVId).ToList();
            
            foreach (ViolationTypeNOV o in novRels)
            {
                nov.ViolationTypeNOVs.Remove(o);                    
                this.UnitOfWork.ViolationTypeNOVRepository.Delete(o.ViolationTypeNOVId);
            }
            novRels.RemoveAll(x => x.NOVId == nov.NOVId);

            List violationNOVs = UnitOfWork.ViolationNOVRepository
                .GetAll().Where(x => x.NOVId == nov.NOVId).ToList();
            foreach (ViolationNOV o in violationNOVs)
            {
                nov.ViolationNOVs.Remove(o);
                this.UnitOfWork.ViolationNOVRepository.Delete(o.ViolationNOVId);
            }
            violationNOVs.RemoveAll(x => x.NOVId == nov.NOVId);

            List caseNOVs = UnitOfWork.CaseNOVRepository
                .GetAll().Where(x => x.NOVId == nov.NOVId).ToList();
            foreach (Case

Lost User

If you used "cascading deletes" you wouldn't have to worry so much about the details. [Cascade Delete - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/saving/cascade-delete)

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food

simpledeveloper

I have set the cascade delete to true - here is my migration filecode for that

public override void Up()
{
DropForeignKey("dbo.Violations", "NOVId", "dbo.NOVs");
DropIndex("dbo.Violations", new[] { "NOVId" });
CreateTable(
"dbo.ViolationTypeNOVs",
c => new
{
ViolationTypeNOVId = c.Int(nullable: false, identity: true),
NOVId = c.Int(nullable: false),
ViolationTypeId = c.Int(nullable: false),
})
.PrimaryKey(t => t.ViolationTypeNOVId)
.ForeignKey("dbo.NOVs", t => t.NOVId, cascadeDelete: true)
.ForeignKey("dbo.ViolationTypes", t => t.ViolationTypeId, cascadeDelete: true)
.Index(t => new { t.NOVId, t.ViolationTypeId }, unique: true, name: "IX_UniqueConstraintViolationTypeNOV");

        CreateTable(
            "dbo.CaseNOVs",
            c => new
                {
                    CaseNOVId = c.Int(nullable: false, identity: true),
                    NOVId = c.Int(nullable: false),
                    CaseId = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.CaseNOVId)
            .ForeignKey("dbo.Cases", t => t.CaseId, cascadeDelete: true)
            .ForeignKey("dbo.NOVs", t => t.NOVId, cascadeDelete: true)
            .Index(t => new { t.NOVId, t.CaseId }, unique: true, name: "IX\_UniqueConstraintCaseNov");
        
        CreateTable(
            "dbo.ViolationNOVs",
            c => new
                {
                    ViolationNOVId = c.Int(nullable: false, identity: true),
                    NOVId = c.Int(nullable: false),
                    ViolationId = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.ViolationNOVId)
            .ForeignKey("dbo.NOVs", t => t.NOVId, cascadeDelete: true)
            .ForeignKey("dbo.Violations", t => t.ViolationId, cascadeDelete: true)
            .Index(t => new { t.NOVId, t.ViolationId }, unique: true, name: "IX\_UniqueConstraintNOVViolation");
        
        AlterColumn("dbo.OneToManies", "ParentEntity", c => c.String());
        AlterColumn("dbo.OneToManies", "ChildEntity", c => c.String());
        //DropColumn("dbo.Violations", "NOVId");
    }

Still why is it not dele

simpledeveloper

Gerry Schmitz wrote:

If you used "cascading deletes" you wouldn't have to worry so much about the details.

Yes I did but still I am going to through this issue - is there anyway I can find which foreign key constraint its failing at or its complaining about. Its just giving me a general statement - its not helpful for me right - if I know column name or foreign key constraint name - then it would be useful - thank you buddy.

simpledeveloper

I have a simple association Table - whose PK isn't referenced anywhere but when I am trying to delete a record from it in the following way - I am getting an Error - I am using EF Code First - any help would be very very helpful - thanks in advance.

List novRels = UnitOfWork.Context.ViolationTypeNOVs.Where(x => x.NOVId == nov.NOVId).Include("ViolationType").Include("NOV").ToList();

foreach (ViolationTypeNOV o in novRels)
{
     UnitOfWork.Context.ViolationTypeNOVs.Remove(o);
}                
UnitOfWork.Context.SaveChanges();

Here is the error message I am getting - if the Tables PK isn't referenced in any way - why is it failing with this error just not able to understand, I am using EF Code First - thank you.

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

The same thing if I am running through SSMS same thing is working though:

Delete ViolationTypeNOVs where ViolationTypeNOVId = 2

Why? But again if I am running the same query through context as below, its giving me the same error at the calling of SaveChanges:

foreach (ViolationTypeNOV o in novRels)
{
string str = string.Format("Delete ViolationTypeNOVs where ViolationTypeNOVId = {0}", new object[] { o.ViolationTypeNOVId });
UnitOfWork.Context.Database.ExecuteSqlCommand(str);
}
UnitOfWork.Context.SaveChanges();

It seems like some of the objects within the context aren't nulling or getting deleted, is there anyway to clear them all in one go? Because these ids/objects are used in multiple places in the code - please let me know how to clear them all - thanks a lot. Any help please?

simpledeveloper

Fixed it - the problem is we need to clear up all the objects and its links that the parent object is using then only we can save the changes thanks here is my solution

    public bool Delete(NOV nov, bool performCommit = true)
    {
        System.Data.Entity.DbContextTransaction dbOperation = null;
        if (performCommit)
            dbOperation = UnitOfWork.BeginTransaction();
        try
        {
            //-- Remove the Items - "foreach" approach was a problem
            // http://weblogs.asp.net/ricardoperes/entity-framework-pitfalls-deleting-orphans

            //------------------------------------------------------
            // Remove the Violations that are in this NOV
            //------------------------------------------------------
            List violationIdlist = new List();

            foreach (var v in nov.ViolationNOVs)
            {
                var a = UnitOfWork.ViolationRepository.GetAll().Where(z => z.ViolationId == v.ViolationId).FirstOrDefault();
                violationIdlist.Add(a);                    
            }

            foreach (var v in violationIdlist)
            {
                var a = nov.ViolationNOVs.Where(x => x.NOVId == nov.NOVId && x.ViolationId == v.ViolationId)?.FirstOrDefault();
                nov.ViolationNOVs.Remove(a);
            }

            nov.IssuedBy.Clear();

            //deleting all OneToMany references to NOV                
            List novRels = UnitOfWork.Context.ViolationTypeNOVs.Where(x => x.NOVId == nov.NOVId).Include("ViolationType").Include("NOV").ToList();                
            nov?.ViolationTypeNOVs?.Clear();                
            //foreach (ViolationTypeNOV o in novRels)
            //{
            //    UnitOfWork.Context.ViolationTypeNOVs.Remove(o);
            //    o?.ViolationType?.ViolationTypeNOVs?.Remove(o);                   
            //    nov?.ViolationTypeNOVs?.Remove(o);
            //}
            UnitOfWork.Context.ViolationTypeNOVs.RemoveRange(novRels);             

            List violationNOVs = UnitOfWork.Context.ViolationNOVs.Where(x => x.NOVId == nov.NOVId).Include("Violation").Include("NOV").ToList();
            nov?.ViolationNOVs?.Clear();
            UnitOfWork.Context.ViolationNOVs.RemoveRange(violationNOVs);