CODEWITHSUNDEEP
c#entity-frameworkcrud
Chris Schiffhauer
Chris Schiffhauer

Reputation: 17310

Setting a Foreign Key Value Back to Null in Entity Framework

I have been unable to save a record to a SQL Server database when removing a foreign key value from the record.

As an example, I have an existing BatchDetail record with a primary key BatchDetailID = 10. And it is associated with an existing RunDetail record that has a primary key RunDetailID = 20, and a foreign key BatchDetailID = 10. I want to disassociate the RunDetail from the BatchDetail by setting RunDetails.BatchDetailID to null.

Here is the error message:

A referential integrity constraint violation occurred: The property value(s) of 'BatchDetail.BatchDetailID' on one end of a relationship do not match the property value(s) of 'RunDetail.BatchDetailID' on the other end.

Here are my class definitions:

public class RunDetail
{
    public int RunDetailID { get; set; }
    public Nullable<int> BatchDetailID { get; set; }

    public virtual BatchDetail BatchDetail { get; set; }
}

public class BatchDetail
{
    public int BatchDetailID { get; set; }

    public virtual ICollection<RunDetail> RunDetails { get; set; }
}

The error occurs when a RunDetail object has a BatchDetailID value, and I try to set it back to null:

public class MyController : Controller
{
    private ISvr _myService;

    public ISvr MyService
    {
        get { return _myService ?? (_myService = new MyHandler().GetMyService()); }

        set
        {
            _myService = value;
        }
    }

    public void UpdateBatch(int id)
    {
        foreach (var batchDetail in MyService.ReadBatchDetails(id))
        {
            var runDetail = MyService.ReadRunDetail(batchDetail.RunDetailID);

            runDetail.BatchDetailID = null;
            runDetail.BatchDetail = null; // I have tried with, and without, this line

            MyService.UpdateRun(runDetail );
        }
    }
}

public class MyHandler
{
    public ISvr GetMyService()
    {
        return new MySvr();
    }
}

[ServiceBehavior(ConcurrencyMode = ConcurrencyMode.Multiple, InstanceContextMode = InstanceContextMode.PerCall)]
public class MySvr : IMySvr
{
    public void UpdateRun(RunDetail runDetail)
    {
        using (var myEntities = new MyEntities())
        {
            myEntities.RunDetails.Attach(runDetail); // This is where the exception hits
            myEntities.Entry(runDetail).State = EntityState.Modified;
            myEntities.SaveChanges();
        }
    }
}

All other record updates, including changing the foreign key value to another, work correctly.

Can anyone see what my flaw is?

Upvotes: 0

Views: 4287

Answers (2)

Chris Schiffhauer
Chris Schiffhauer

Reputation: 17310

The problem was rooted in a method that I did not include in my question. When I retrieved the parent BatchDetail record, I included the "RunDetails" records:

public List<BatchDetail> ReadBatchDetails(int id)
{
    using (var myEntities = new MyEntities())
    {
        return myEntities.BatchDetails
            .Include("RunDetails") // This is the problem
            .AsNoTracking().ToList();
    }
}

By creating a new method that does not Include("RunDetails"), the problem is resolved:

public List<BatchDetail> ReadBatchDetailsWithoutRunDetails(int id)
{
    using (var myEntities = new MyEntities())
    {
        return myEntities.BatchDetails.AsNoTracking().ToList();
    }
}

Upvotes: 1

Yuliam Chandra
Yuliam Chandra

Reputation: 14640

I inserted 1 BatchDetail and 1 RunDetail as follow.

using (var db = new AppContext())
{
    db.BatchDetails.Add(new BatchDetail { Id = 1 });
    db.RunDetails.Add(new RunDetail { Id = 1, BatchDetailId = 1 });
    db.SaveChanges();
}

Then here is the result that throws the same error.

using (var db = new AppContext())
{
    // This is okay.
    var x = new RunDetail { Id = 1, BatchDetailId = 1, BatchDetail = new BatchDetail { Id = 1 } };
    db.RunDetails.Attach(x);
}
using (var db = new AppContext())
{
    // This is okay.
    var x = new RunDetail { Id = 1, BatchDetailId = null, BatchDetail = null };
    db.RunDetails.Attach(x);
}
using (var db = new AppContext())
{
    // This is okay.
    var x = new RunDetail { Id = 1, BatchDetailId = 1, BatchDetail = null };
    db.RunDetails.Attach(x);
}
using (var db = new AppContext())
{
    // A referential integrity constraint violation occurred: The property 
    // value(s) of 'BatchDetail.Id' on one end of a relationship do not match the property 
    // value(s) of 'RunDetail.BatchDetailId' on the other end.
    var x = new RunDetail { Id = 1, BatchDetailId = null, BatchDetail = new BatchDetail { Id = 1 } };
    db.RunDetails.Attach(x);
}

It should be the same in your case.

Upvotes: 0

Related Questions