none
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 su RRS feed

  • Question

  • I'm getting this fairly common error when I try to update my entities.

    My POCO entities are setup as follows:

    public class R
        {
            public R()
            {
                Gs = new HashSet<G>();
                Res = new HashSet<RB>();
            }
    
            public int RID { get; set; }
    
            public int EID { get; set; }
            public virtual E E { get; set; }
    
            public string Logon { get; set; }
    
            public string FullName { get; set; }
            
            [Required]
            public string Dept { get; set; }
    
            [Required]
            public string AType { get; set; }
    
            public int Number { get; set; }
            
            public int IdentifierField { get; set; }
    
            [InverseProperty("R")]
            public ICollection<G> Gs { get; set; }
    
            public ICollection<RB> Res { get; set; }
    
            public ICollection<RB> RRes
            {
                get { return Res.Where(x => x.QB.QBID == 1).ToList(); }
            }
    
            public ICollection<RB> GRes
            {
                get { return Res.Where(x => x.QB.QBID == 2).ToList(); }
            }
    
            public string CreatedBy { get; set; }
            public DateTime CreatedDate { get; set; }
            public string UpdatedBy { get; set; }
            public DateTime? UpdatedDate { get; set; }
        }
    
    
        public abstract class RB
        {
            public int RBID { get; set; }
    
            public int RID { get; set; }
            public virtual R R { get; set; }
    
            public int? GID { get; set; }
            public virtual G G { get; set; }
    
            public int QBID { get; set; }
            public virtual QB QB { get; set; }
        }
    
    
        public class G
        {
            public G()
            {
                Res = new HashSet<RB>();
            }
    
            public int GID { get; set; }
    
            public int GNumber { get; set; }
            
            public string GName { get; set; }
    
            public int RID { get; set; }
    
            [InverseProperty("G")]
            public virtual R R { get; set; }
    
            public ICollection<RB> Res { get; set; }
        }


    And I have my View Model

    public class RVM : R
        {
            public RVM()
            {
                RRes = new HashSet<RB>();
                GRes = new HashSet<RB>();
            }
    
            public string Message { get; set; }
    
            public List<SelectListItem> AllDepts { get; set; }
    
            public IEnumerable<string> AllATypes { get; set; }
    
            public IEnumerable<SelectListItem> GDDL { get; set; }
            public string Name { get; set; }
            new public ICollection<RB> RRes { get; set; }
            new public ICollection<RB> GRes { get; set; }
        }


    I'm also using AutoMapper:

              Mapper.CreateMap<RVM, R>()
                      .ForMember(src => src.Res, opt => opt.ResolveUsing(ResConverter))
                      .ForMember(dest => dest.RRes, opt => opt.Ignore())
                      .ForMember(dest => dest.GRes, opt => opt.Ignore());
    
    private ICollection<RB> ResConverter(RVM x)
    {
        var r = x.RRes.Concat(x.GRes).ToList();
        return r;
    } 

    In my controller, I am trying to update using the following code:

    var r = repository.Load<R>(x => x.Res, y => y.Gs).Single(x => x.RID == rVM.RID);
    Mapper.DynamicMap(rVM, r);
    repository.Update(r);
    repository.Save();

    And I get the error on repository.Save: 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.

    I'm not sure what I'm doing wrong...Please help!

    Moneka

    Wednesday, September 4, 2013 5:44 PM

Answers

  • Hello,

    Thanks for posting your problem to this forum.

    In my understanding, the exception “The relationship could not be changed because one or more of the foreign-key properties is non-nullable” is a very big problem. What actually happens in your code is this:

    1. You load Parent from the database and get an attached entity
    2. You replace its child collection with new collection of detached children
    3. You save changes but during this operation all children are considered as added becasue EF didn't now about them till this time. So EF tries to set null to foreign key of old children and insert all new children => duplicate rows.

    Now the solution really depends on what you want to do and how would you like to do it?

    If we use ASP.NET MVC so we can try to use UpdateModel or TryUpdateModel. 

    If we want just update existing children manually, we can simply do something like:

    foreach (var child in modifiedParent.ChildItems)
    {
        context.Childs.Attach(child); 
        context.Entry(child).State = EntityState.Modified;
    }
    context.SaveChanges();

    Attaching is actually not needed (setting the state to Modified will also attach the entity) but it makes the process more obvious.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Thursday, September 5, 2013 3:41 AM
    Moderator
  • Hi,

    If you want to update with child table, you need turn on like below:

    ALTER TABLE table1 ADD
            CONSTRAINT [FK_Table1_Table2]
    FOREIGN KEY
    (
      field1
    ) REFERENCES table2 (
             field1
            ) ON UPDATE CASCADE ON DELETE CASCADE
    Regards.
    Friday, September 6, 2013 9:09 AM

All replies

  • Or if there's any way I can debug this error to figure out which field has an issue - I have been struggling with it for a long time...thanks!
    Wednesday, September 4, 2013 10:48 PM
  • Hello,

    Thanks for posting your problem to this forum.

    In my understanding, the exception “The relationship could not be changed because one or more of the foreign-key properties is non-nullable” is a very big problem. What actually happens in your code is this:

    1. You load Parent from the database and get an attached entity
    2. You replace its child collection with new collection of detached children
    3. You save changes but during this operation all children are considered as added becasue EF didn't now about them till this time. So EF tries to set null to foreign key of old children and insert all new children => duplicate rows.

    Now the solution really depends on what you want to do and how would you like to do it?

    If we use ASP.NET MVC so we can try to use UpdateModel or TryUpdateModel. 

    If we want just update existing children manually, we can simply do something like:

    foreach (var child in modifiedParent.ChildItems)
    {
        context.Childs.Attach(child); 
        context.Entry(child).State = EntityState.Modified;
    }
    context.SaveChanges();

    Attaching is actually not needed (setting the state to Modified will also attach the entity) but it makes the process more obvious.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Thursday, September 5, 2013 3:41 AM
    Moderator
  • I'd like to update everything: the parent as well as all the children in one call.

    I tried using UpdateModel as follows, however it added an extra G record... TryUpdateModel didn't do much either...

    var r= repository.Find<R>(rVM.RID);
    UpdateModel(r);
    repository.Update(r, this.GetNTLogin());
    repository.Save();

    Please help thanks!

    Thursday, September 5, 2013 10:01 PM
  • Hi,

    If you want to update with child table, you need turn on like below:

    ALTER TABLE table1 ADD
            CONSTRAINT [FK_Table1_Table2]
    FOREIGN KEY
    (
      field1
    ) REFERENCES table2 (
             field1
            ) ON UPDATE CASCADE ON DELETE CASCADE
    Regards.
    Friday, September 6, 2013 9:09 AM
  • My issue isn't resolved as of yet however Fred, your solution brought me closer to a conclusion... Hopefully I will have a resolution soon...Thanks!
    Wednesday, September 11, 2013 4:45 PM