EF Seed not working when data already in database

Question

First time creating DB and running Seed method works fine. It throws error on second run when data is already in the database.

I also noticed that when I assign property to entity, its PropertyId is not updated and stays null.

This is my code.

protected override void Seed(StreetStats.Data.StreetStatsDbContext context)
{
    if (System.Diagnostics.Debugger.IsAttached == false)
        System.Diagnostics.Debugger.Launch();

    using (context.Database.BeginTransaction())
    {
        try
        {
            Worker worker = new Worker()
            {
                Name = "Worker 1",
            };

            context.Workers.AddOrUpdate(w => w.Name, worker);
            context.SaveChanges(); //Worker gets Id assigned to the existing worker with the same name in DB

            Job job = new Job()
            {
                Name = "Job 1",                     
                Worker = worker
            };

            context.Jobs.AddOrUpdate(j => j.Name, job);
            context.SaveChanges(); //WorkerId is null for some reason

            MonitoringTask monitoringTask = new MonitoringTask
            {
                Job = job,
                Name = "Task 1"                     
            };

            context.MonitoringTasks.AddOrUpdate(mt => mt.Name, monitoringTask);
            context.SaveChanges(); //Throws exception

            Area area = new Area
            {
                MonitoringTask = monitoringTask,
                Name = "Area 1"                     
            };

            context.Areas.AddOrUpdate(a => a.Name, area);
            context.SaveChanges();

            context.Database.CurrentTransaction.Commit();
        }
        catch (Exception)
        {
            context.Database.CurrentTransaction.Rollback();
            throw;
        }
    }
}

This is exception message on third SaveChanges:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_dbo.MonitoringTasks_dbo.Jobs_JobId". The conflict occurred in database "StreetStats", table "dbo.Jobs", column 'Id'.

Every FK relation looks like

Worker Worker { get; set; }
Int64 WorkerId { get; set; }

Answer 1

I noticed this also; just add the Id field for each property you are adding in your seed method.

i.e.

Worker worker = new Worker()
{
    WorkerId = 1,
    Name = "Worker 1",
};

context.Workers.AddOrUpdate(w => w.Name, worker);
context.SaveChanges();

Job job = new Job()
{
    JobId = 1,
    Name = "Job 1",                     
    Worker = worker.WorkerId
};

context.Jobs.AddOrUpdate(j => j.Name, job);
context.SaveChanges(); //WorkerId is null for some reason

MonitoringTask monitoringTask = new MonitoringTask
{
    MonitoringTaskId = 1,
    Job = job.JobId,
    Name = "Task 1"                     
};

context.MonitoringTasks.AddOrUpdate(mt => mt.Name, monitoringTask);
context.SaveChanges();

This worked for me.

Answer 2

Look at the entity state of job after its AddOrUpdate call in the second run. You can do this like so:

Debug.WriteLine(context.Entry(job).State);

You'll see that it's Detached. Still, if you do ...

Debug.WriteLine(context.Jobs.Local.First().Name);

... you'll see that in reality the job is attached to the context!

This a known bug in AddOrUpdate. The instance that's actually attached to the context is hidden from your method scope, and job is a second instance that EF doesn't know of.

That causes all kinds of misery. The job you connect with monitoringTask will be seen as a new instance and EF will try to insert it. I'm not sure why you get a foreign key exception (I would have expected a unique key violation) but I think that has to do with primary key column type and whether or not it has an identity specification.

Anyway, the work-around is to do ...

context.Workers.AddOrUpdate(w => w.Name, worker);
context.SaveChanges();
worker = context.Workers.Local.Single(w => w.Name == worker.Name);

... and so on, for each AddOrUpdate call of which you intend to use the objects later. This makes the actually attached (but hidden) objects equal to the ones visible to you.