How do you update a table with a foreign key to another table in ADO.Net Entity Model?

Question

I have 2 tables, Table1 has a primary key 'CustomizationId', and Table2 has a FK Customizationid which matches this. Table2 has no primary key.

I am trying to add a new record from a web based form. I attempt to save this to the database and I get an error:

            Customization customization = new Customization();
            Code code = new Code();
            customization.Name = CustomizationName.Text;               
            customization.LastUpdated = DateTime.Now;

            code.Top = top_js.InnerText;
            code.Bottom = bottom_js.InnerText;

            //code.CustomizationId = customization.CustomizationId;

            customization.Code = code;              
            entities.AddToCustomizations(customization);
            entities.SaveChanges();

When I call SaveChanges I am getting an error, whether or not I add in the commented line.

Unable to update the EntitySet 'Code' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

How do I handle this situation? I just want to add in the code at the same time that I add in the Customization. The 'Code' table should have the Customizationid set to the PK/Identity set by Customization.

Any ideas?

Answer 1

As far as the EF is concerned the table without a PK is a View.

This means you have two options:

1. Tell a little white lie, and convince the EF that the 'view' is a table
2. Add modification functions (Insert/Update/Delete) so you can edit them.

Generally modification functions are stored procedures, but you can actually add T-SQL directly to the SSDL if you don't have access to the database...

I.e. something like this in the <StorageModel> element of the EDMX for each action (insert, update and delete):

<Function Name="InsertCode" BuiltIn="false" IsComposable="false" >
       <CommandText>
                INSERT dbo.TCode(ID, Name) VALUES (@ID, @Name)
       </CommandText>
       <Parameter Name="ID" Type="int" Mode="In" />
       <Parameter Name="ID" Type="nvarchar(max)" Mode="In" />
</Function>

Once you have the modification functions in your SSDL you need to map them so that the EF uses them as required.

In your situation I recommend (1).

Hope this helps.

Cheers Alex