How can I set the value of a Navigation Property in the code behind?

Question

I have an Entity called Cost, which has a required property of CostType

The Cost class has a GetNew() method which sets all the Cost's defaults:

public static GetNew()
{
    Cost cost = new Cost ();
    foo.CostType = Lists.CostTypes.FirstOrDefault();
    // Other Default Values

    return foo;
}

The Lists.CostTypes is a static list which is pulled from EF at startup and used in ComboBoxes

I am having problems setting the CostType within my code, after first setting it in the GetNew() method.

For example, the following code reads an excel file, and sets the default type based on a column in the Excel file, or null if it can't find a match

Cost cost = Cost.GetNew();
cost.CostType = Lists.CostTypes.FirstOrDefault(t => t.Name == row[0].ToString());

My problem is, during the Save operation I get the following error:

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

My Add Operation looks like this:

public static void AddObject(EntityObject obj, string entitySetName)
{
    context.AddObject(entitySetName, obj);
    context.SaveChanges();
}

• If I remove the line of code that manually sets the Cost when it reads the excel file, the save works fine.
• If I change the line of code to read Lists.Costs[2], it saves fine.
• If I remove the line of code in GetNew() which sets the default, I get an error telling me that I violated the PK rule of CostTypes, meaning it's trying to insert the Cost Type.
• Changing the ComboBox showing Type to something else still gives the same error.
• After loading costs from the excel file, my regular Add/Edit forms throw the same error when I change the Type and try and save. If I don't load an excel file, they work fine.

I'm still learning Entity Framework, but so far it has been nothing but a frustration and a headache to use. Does someone know what my problem is and how I can fix it?

EDIT

Here's the info requested by Slauma. I am keeping it simple and excluding unrelated objects

• Costs are in one table and CostTypes are in another table. In the database, the Costs.TypeId column is not allowed to be null, and is a Foreign Key to CostTypes. The Id field for both tables is auto-generated.

• My EF model is just a generic one with the two database tables added. The only change I made to it was to rename some fields and remove the CostTypes.Costs Navigation Property.

• The Excel file that gets imported maps most costs to their matching CostType.Name, however it IS possible that the string in the excel file doesn't match a CostType, so Lists.CostTypes.FirstOrDefault(t => t.Name == row[0].ToString()) can assign aNULLvalue to theCost.Typeproperty. That doesn't seem to be a problem though, because the form still comes up with the list of costs and their default selected items. Item's with aNULLCostType do not have an item selected in the CostTypeComboBox` and trigger a validation error that must be corrected before saving.

The code to load the CostType list is

public static List<T> GetList<T>(string sortProperty)
    where T : EntityObject
{
    using (var context = new TContext())
    {
        return ApplyOrder<T>(context.CreateObjectSet<T>(), sortProperty, "OrderBy").ToList();
    }
}

The ApplyOrder code can be found here.

The GetList method is called from

public static class Lists
{
    public static List<CostType> CostTypes { get; private set; }

    static Lists()
    {
        CostTypes = DAL<CostEntities>.GetList<CostType>("Name");
    }
}

Answer 1

I figured it out.... it was a mix of a few different things

Creating a new Cost and setting the Type was adding the cost to the shared data context. If that Cost wasn't included in the list of costs to save, or it failed it's validation error, or the user cancelled out of the Import dialog, the cost still existed in context.ObjectStateManager._addedObjects, even though I never called AddObject or AttachObject. Once I realized that I started callling DeleteObject on costs that were not going to be saved and it cleared up the 1st error I was getting.

The 2nd error I was getting (duplicate PK) was because I was looping through my new Costs and calling AddObject and SaveChanges on each one. Since setting Cost.Type to an attached CostType was automatically adding my Cost to the context, the first cost to get saved was actually adding all the new Costs to the database while the 2nd cost was trying to call AddObject/SaveChanges on what EF saw as an object that already existed

Answer 2

Here is not really a satisfying answer but a mix of guesses and open questions based on your infos in the question and in the comments to your question:

• First of all: Your list Lists.CostTypes contains obviously entities which are detached from the context where you are later adding and saving new objects in. Because you have a using block: using (var context = new TContext()) you are retrieving your CostType entities in another context.

• To tell EF that these CostType entities already exist in the database you must attach the entities to your second context (context.CostTypes.Attach(costType)) where you save your changes in (or use the same context in your method where you retrieve the list). I don't see in your code that you do this. (CostType is a navigation reference property, not a foreign key property, right?)

• On the other hand when the CostType entities are not attached you should get duplicated CostTypes in your database because EF will consider them as new objects (to insert in the DB) when you call AddObject for your Cost entity since EF will always put the whole object graph of detached entities into Added state. Do you get duplicated CostTypes in the DB in your working examples? If not, something important is missing in your code snippets.

• The last paragraph assumes that the key for CostType is autogenerated in the DB, as you said. If not, you would get a PK constraint violation instead of duplicated entities.

• If the keys for CostType and Cost are really autogenerated identities, I am wondering where the PK violation you mentioned can come from. Every insertion would create a new unique primary key. There could never a PK violaton occur. Can you show the exception message in detail?

• Did you check that all Cost entities you want to save really have a non-null CostType property (after the user has fixed all validation errors)? I cannot see any other possible reason in your code why you would get your "Relationship-could-not-be-changed-exception", except that at least for one of the Cost objects CostType is null.