Entity Framework Migrations - A referential integrity constraint violation

Question

I've been struggling with this for a while and can't find a solution on Stack Overflow.

I've got an MVC App which uses Entity Framework with Repository and Unit of Work patterns. I have recently moved from using a network database (Sql Server 2008 R2, where the issue didn't seem to exist) to local database and started using Entity Framework migrations in order to work with another developer and not affect each other by making changes to our models.

My model looks like this:

[Table("Student")]
public class Student
{
    [Key]
    public int Id { get; set; }

    <... other fields ...>

    [Required(ErrorMessage = "A student type is required")]
    public int StudentTypeId { get; set; }

    [Required(ErrorMessage = "A student status is required")]
    public int StudentStatusId { get; set; }

    [ForeignKey("StudentTypeId")]
    public virtual StudentType StudentType { get; set; }

    [ForeignKey("StudentStatusId")]
    public virtual StudentStatus StudentStatus { get; set; }
}

Every time I try to update StudentStatus property of Student I get the following exception:

"The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: A referential integrity constraint violation occurred: The property value(s) of 'StudentStatus.Id' on one end of a relationship do not match the property value(s) of 'Student.StudentStatusId' on the other end."

I've tried re-setting the navigation property to null before saving the changes.

student.StudentStatus = null;
student.StudentStatusId = 26;
_studentRepository.Update(student);
_unitOfWork.Commit();

I've tried retrieving a specific StudentStatus object:

var studentStatus = _studentStatusRepository.GetById(26);
student.StudentStatusId = 26;
student.StudentStatus = studentStatus;
_studentRepository.Update(student);
_unitOfWork.Commit();

but every try it throws the same exception on DataContext.SaveChanges().

I can update StudentType (which is literally the same kind of relationship and similar class) without any problem.

Implementation of Update method:

public virtual void Update(T entity)
{
    try
    {
        DataContext.Entry(entity).State = EntityState.Modified;
    }
    catch (Exception exception)
    {
        throw new EntityException(string.Format("Failed to update entity '{0}'", typeof(T).Name), exception);
    }
}

I have noticed that EF Migrations created more indexes in the database, compared to my previous network database, but I have since deleted them manually (not that I thought they would necessarily be the problem). I tried comparing relationships in the two databases and everything seems to be the same.

I know it might be difficult to point out what might be wrong without me providing more information, but what else could possibly be causing this issue?

EDIT (added StudentStatus class and corresponding StudentStatusType)

[Table("StudentStatus")]
public class StudentStatus
{
    [Key]
    public int Id { get; set; }

    [Required(ErrorMessage = "Student status name is required")]
    [MaxLength(50, ErrorMessage = "Student status name cannot be longer than 50 characters")]
    public string Name { get; set; }

    public int StudentStatusTypeId { get; set; }

    [ForeignKey("StudentStatusTypeId")]
    public virtual StudentStatusType StudentStatusType { get; set; }
}

[Table("StudentStatusType")]
public class StudentStatusType
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

EDIT 2

I forgot to mention that I enabled database logging on repository level to see what SQL queries are being executed by Entity Framework:

DataContext.Database.Log = s => Debug.WriteLine(s); 

The result is:

UPDATE [dbo].[Student]
SET <... some parameters ...>, [StudentStatusId] = @10, <... some parameters ...>
WHERE ([Id] = @14)

UPDATE [dbo].[Student]
SET <... some parameters ...>, [StudentStatusId] = @10, <... some parameters ...>
WHERE ([Id] = @14)

<... some parameters ...>

-- @10: '25' (Type = Int32)

-- @10: '25' (Type = Int32)

<... some parameters ...>

-- Executing at 12/01/2015 12:30:41 +00:00

-- Executing at 12/01/2015 12:30:41 +00:00

-- Completed in 0 ms with result: 1

-- Completed in 0 ms with result: 1

So why is EF trying to insert value of 25, even though I explicitly specify it to be 26? Is this causing the underlying issue? Also, why would there be two update statements and not one?

Answer 1

After Adimeus's suggestion I had to investigate how the initial data was seeded.

StudentStatus as opposed to StudentType was seeded in my service layer (instead of Configuration.cs file of EF Migrations) - don't ask me why; it was another developer dealing with migrations.

Was:

if (!_studentStatusRepository.GetAll().Any())
{
    var newStudentStatus = _studentStatusTypeRepository.Get(x => x.Name == "New");
    var activeStudentStatus = _studentStatusTypeRepository.Get(x => x.Name == "Active");
    var deletedStudentStatus = _studentStatusTypeRepository.Get(x => x.Name == "Deleted");

    var studentStatuses = new List<StudentStatus>
    {
        new StudentStatus {Name = "New", StudentStatusType = newStudentStatus, StudentStatusTypeId = newStudentStatus.Id},
        new StudentStatus {Name = "Awaiting Approval", StudentStatusType = activeStudentStatus, StudentStatusTypeId = activeStudentStatus.Id},
        new StudentStatus {Name = "Approved", StudentStatusType = activeStudentStatus, StudentStatusTypeId = activeStudentStatus.Id},
        new StudentStatus {Name = "Deleted", StudentStatusType = deletedStudentStatus, StudentStatusTypeId = deletedStudentStatus.Id},
        new StudentStatus {Name = "Reinstated", StudentStatusType = deletedStudentStatus, StudentStatusTypeId = deletedStudentStatus.Id}
    };

    foreach (var studentStatus in studentStatuses.ToList())
    {
        StudentStatus status = studentStatus;
        var dbStudentStatus = _studentStatusRepository.Get(x => x.Name == status.Name);
        if (dbStudentStatus == null)
        {
            _studentStatusRepository.Add(studentStatus);
        }
    }
    _unitOfWork.Commit();
}

I have moved the seeding to EF Migrations Configuration.cs file (I suppose it can probably be optimised, but it was a quick test):

var studentStatuses = new List<StudentStatus>
{
    new StudentStatus
    {
        Name = "New",
        StudentStatusType = context.StudentStatusTypes.Single(x => x.Name == "New"),
        StudentStatusTypeId = context.StudentStatusTypes.Single(x => x.Name == "New").Id,
    },
    new StudentStatus
    {
        Name = "Awaiting Approval",
        StudentStatusType = context.StudentStatusTypes.Single(x => x.Name == "Active"),
        StudentStatusTypeId = context.StudentStatusTypes.Single(x => x.Name == "Active").Id,
    },
    new StudentStatus
    {
        Name = "Approved",
        StudentStatusType = context.StudentStatusTypes.Single(x => x.Name == "Active"),
        StudentStatusTypeId = context.StudentStatusTypes.Single(x => x.Name == "Active").Id,
    },
    new StudentStatus
    {
        Name = "Deleted",
        StudentStatusType = context.StudentStatusTypes.Single(x => x.Name == "Deleted"),
        StudentStatusTypeId = context.StudentStatusTypes.Single(x => x.Name == "Deleted").Id,
    },
    new StudentStatus
    {
        Name = "Reinstated",
        StudentStatusType = context.StudentStatusTypes.Single(x => x.Name == "Deleted"),
        StudentStatusTypeId = context.StudentStatusTypes.Single(x => x.Name == "Deleted").Id,
    }
};

studentStatuses.ForEach(x => context.StudentStatuses.AddOrUpdate(y => y.Name, x));

The issue is gone! I can update StudentStatus now. Don't quite understand why seeding in the service caused the issue in the first place. If anyone can explain, I will accept the answer; otherwise I will accept my answer in a few days time.

Answer 2

EF might be getting confused by the navigational property on StudentStatus not being related to Student at all. Adding in the following line to the StudentStatus class should do the trick.

public virtual ICollection<Student> Students { get; set; }

Answer 3

To me, this approach seems far more intuitive:

int StudentStatusType holds the value of StudentStatusType.Id, so it should be marked [ForeignKey]. If you add a virtual StudentStatusType-property, EntityFramework will bind it automatically.

[Table("StudentStatus")]
public class StudentStatus
{
    [Key]
    public int Id { get; set; }

    [Required(ErrorMessage = "Student status name is required")]
    [MaxLength(50, ErrorMessage = "Student status name cannot be longer than 50 characters")]
    public string Name { get; set; }

    [ForeignKey("StudentStatusType")] 
    public int StudentStatusTypeId { get; set; }
    public virtual StudentStatusType StudentStatusType { get; set; }
}

[Table("StudentStatusType")]
public class StudentStatusType
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}