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