EntityFramework's AddOrUpdate leads to incorrect foreign key update

Question

Suppose I have a number of countries, each of which has a number of cities. I can represent this using the following models:

public class Country
{
    public virtual int Id { get; set; }
    [Required]
    public virtual string Name { get; set; }

    public virtual ICollection<City> Cities { get; set; }
}

public class City
{
    public virtual int Id { get; set; }
    [Required]
    public virtual string Name { get; set; }

    public virtual Country Country { get; set; }
    public virtual int CountryId { get; set; }
}

class TestContext : DbContext
{
    public DbSet<City> Cities { get; set; }
    public DbSet<Country> Countries { get; set; }
}

Entity Framework correctly identifies the foreign keys and generates the tables.

However, if I now try to seed some test data:

static class Program
{
    static void Main()
    {
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<TestContext>());
        using (var db = new TestContext())
        {
            db.Database.Initialize(true);
            db.Database.Log = Console.Write;

            var country = db.Countries.Create();
            country.Name = "France";
            db.Countries.AddOrUpdate(a => a.Name, country);

            var city = db.Cities.Create();
            city.Name = "Paris";
            city.Country = country;
            db.Cities.AddOrUpdate(q => q.Name, city);

            db.SaveChanges();
        }
    }
}

The first time this is run, all is fine, and the CountryId field in the database is set properly. However, when I run it a second time, db.SaveChanges() attemps to set the CountryId of Paris to 0, which violates the non-nullable foreign key constraint.

The problem seems to be that despite city and country being change-tracking proxies, the CountryId property is never updating. Updating it manually doesn't help this, though.

Is this the intended behaviour, and if so, how can I use AddOrUpdate without changing like this? Doing everything by setting foreign keys doesn't seem to be an option, as those aren't available the first time the code runs.

Answer 1

AFAIK AddOrUpdate doesn't support navigation properties. Also when AddOrUpdate doesn't get any property by input it will save it with default value of property.

In your stiuation you use navigation property.

 city.Name = "Paris";
 city.Country = country;

But AddOrUpdate doesn't assign city.CountryId=country.Id by internally because of lack of support navigation property. So your city.CountryId value becomes 0 which is default value for int.

You should assign foreign key by yourself.

 var city = db.Cities.Create();
 city.Name = "Paris";
 city.Country = country;
 db.Cities.AddOrUpdate(q => q.Name, city);

should be like this

 var city = db.Cities.Create();
 city.Name = "Paris";
 city.Country = country;
 //Assign foreign key by manually.
 city.CountryId = country.Id;
 db.Cities.AddOrUpdate(q => q.Name, city);

I didn't test it.