I am having trouble with foreign key relationships in the entity framework. I have two tables: Persons and Countries. Persons has a foreign key column to CountryId.

As the Countries table rarely changes, I want to fetch its data only once, dispose the DatabaseContext, and keep the list of Countries cached somewhere. This is where I am running into problems.

The entity framework seems to want you to open a database context, add/edit rows as needed, then close the database context. If you open, fetch data, close; and then later open, save data, close; it has trouble.

So my POCO objects look like this:

public class Country {
    public int CountryId {get; set; }
    public String Name {get; set; }
}

public Person {
    public int PersonId {get; set; }
    public virtual Country Country {get; set; }
}

Then, I try to create a new person like this:

Country[] countries;
using (var dt = new DatabaseContext())
{
    countries= dt.Countries.ToArray();
}

Person person = new Person();
person.Country = countries[0];
using (var dt = new DatabaseContext()) {
    dt.Entities.Add(person);
    dt.SaveChanges();
}

On save, the entity framework creates a new row in the Countries table with the same name as countries[0], but a new, incremented ID. This is obviously not the desired outcome - the person should have its Country_CountryId field set to the id of countries[0], and a new row should not be created.

How do I solve this? I think one solution would be to force the entity framework to not create a new row when it is given an object that already has its primary key set. Is there a way to do this?

I wonder if you at least search little bit on Internet before you put quite big effort to describe the issue because this is very common problem asked every few days.

Add method adds all entities in the entity graph. So if you connect country to person and country is not attached to current context, calling Add on the person will mark both person and country as new entities for insertion. If you don't want country to be inserted you must tell EF that country is not a new entity:

Person person = new Person();
person.Country = countries[0];
using (var dt = new DatabaseContext()) {
    dt.Entities.Add(person);
    dt.Entry(person.Country).State = EntityState.Modified;
    dt.SaveChanges();
}

more