Deleting items from collection with EF6 ( 1 to many relationship)

Question

I have following two classes: There are 1 to many relationship between project and schedulePhases. I am trying to remove phases from SchedulePhases collection. But it throws an exception on SaveChanges().

Exception: An error has occurred. 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. System.InvalidOperationException at System.Data.Entity.Core.Objects.ObjectContext.PrepareToSaveChanges(SaveOptions options) at System.Data.Entity.Core.Objects.ObjectContext.d__31.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at AddProjectODataService.Controllers.ProjectsController.<Patch>d__a.MoveNext() in c:\Workspace\VS2013\POC\AddProjectODataService\AddProjectODataService\Controllers\ProjectsController.cs:line 142 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Threading.Tasks.TaskHelpersExtensions.d__31.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Controllers.ApiControllerActionInvoker.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()

Project.cs:

public class Project 
{      

    public Project()
    {
        SchedulePhases = new HashSet<SchedulePhase>();
    }

    public void Initialize()
    {                       
        CalculateSchedule();
    }


    [Key]
    public decimal ProjectId { get; set; }

    public decimal AssetId { get; set; }

    public decimal CapitalCategoryId { get; set; }

    public decimal ProjectTypeId { get; set; }          

    public virtual Asset Asset { get; set; }
    public virtual ICollection<SchedulePhase> SchedulePhases { get; set; }
    public virtual CapitalCategory CapitalCategory { get; set; }     
    public virtual ProjectType ProjectType { get; set; }        

    public void CalculateSchedule()
    {
        List<SchedulePhase> SchedulePhaseList = new List<SchedulePhase>();
        SchedulePhase sp = new SchedulePhase();

        if (this.ProjectTypeId == 15)
        {
            if (this.SchedulePhases.Count > 0)
            {                   
               foreach (SchedulePhase phase in SchedulePhases.ToList())
                {                        
                    SchedulePhases.Remove(phase);
                }           
            }
         }
    }

SchedulePhase.cs:

 public class SchedulePhase
 {
    public SchedulePhase()
    {

    }

    [Key]
    public decimal SchedulePhaseId { get; set; }

    public decimal ProjectId { get; set; } //FK 

    public decimal PhaseTypeId { get; set; }

    public DateTime StartDate { get; set; }

    [StringLength(1)]
    public string ActualEstimateCalcStart { get; set; }

    public DateTime EndDate { get; set; }

    [StringLength(1)]
    public string ActualEstimateCalcEnd { get; set; }

    public decimal Duration { get; set; }

    public decimal? CostingYear { get; set; }

    public decimal? Cost { get; set; }

    public decimal OffSet { get; set; }

    public virtual Project Project { get; set; }

    public virtual PhaseType PhaseType { get; set; }
}

Here is my patch operation in ProjectController.cs (I am using Odata V3 with web api) :

     // PATCH: odata/Projects(5)
    [AcceptVerbs("PATCH", "MERGE")]
    public async Task<IHttpActionResult> Patch([FromODataUri] decimal key, Delta<Project> patch)
    {
        Validate(patch.GetEntity());

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        Project project = await db.Projects.FindAsync(key);
        if (project == null)
        {
            return NotFound();
        }

        patch.Patch(project);
        project.Initialize();

        try
        {
            await db.SaveChangesAsync(); //Above Exception thrown here.
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!ProjectExists(key))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return Updated(project);
    }

I have read few articles but not sure what to do to resolve in my scenario.

I have referred following articles: http://blogs.msdn.com/b/dsimmons/archive/2010/01/31/deleting-foreign-key-relationships-in-ef4.aspx http://msdn.microsoft.com/en-us/data/jj713564.aspx Entity Framework .Remove() vs. .DeleteObject() ( In this, User suggested to explicitly delete the child with DeleteObject. I am using DbContext.)

Any suggestion?

Thanks,

Are you able to query the ScheduledPhases property from the Project object? What does the actual table structure look like? — John, Nov 19 '14 at 22:05
You don't show the most relevant part. What happens in `patch.Patch(project)` and `project.Initialize()`? — Gert Arnold, Nov 19 '14 at 22:47
@John, Yes. I am able to query from the project object. CREATE TABLE OCPDS_POC.TBLSCHEDULEPHASE ( SCHEDULEPHASEID NUMBER NOT NULL, PROJECTID NUMBER NOT NULL, PHASETYPEID NUMBER NOT NULL, STARTDATE DATE NOT NULL, ACTUALESTIMATECALCSTART CHAR(1 BYTE) NOT NULL, ENDDATE DATE NOT NULL, ACTUALESTIMATECALCEND CHAR(1 BYTE) NOT NULL, DURATION NUMBER NOT NULL, OFFSET NUMBER NOT NULL, COSTINGYEAR NUMBER, COST NUMBER(24,6) ) — user659469, Nov 20 '14 at 14:45
@Arnold, the code for Project.Initialize() is inside Project.cs page. And for patch.Patch(Project) - I don't write Patch() method. It's inbuilt Delta.Patch(TEntityType original) method for Overwrites the original entity with the changes tracked by this Delta. — user659469, Nov 20 '14 at 14:49

score 0 · Answer 1 · answered Nov 21 '14 at 15:14

What the exception is trying to tell you is that you have a dependent relationship between Project and SchedulePhase objects (as denoted by the ProejctId property of the SchedulePhase class).

When you call Project.SchedulePhases.Remove, EF is tries to sever the relationship between the two objects without actually deleting the child (SchedulePhase) object. It does this by trying to set the value of the foreign key property (ProjectId in this case) to a value of null. Since the value of the property (decimal in this case) is non-nullable, EF doesn't know what to do. It expects that you will change the value of the ProjectId property to point to a different Project instance instead.

Performing the DeleteObject or its DbContext counterpart (remember DbContext is just a wrapper of the most common methods of ObjectContext) actually deletes the SchedulePhase object from the database.

Arthur Vickers of the EF team wrote a nice entry on his blog a couple of years ago here that explains this exception in more detail. It even includes some code for automatically removing the related entities but I could never get it to work.

Deleting items from collection with EF6 ( 1 to many relationship)

1 Answers1