I see that some people do not know how to update model and complex type if they have to change stored procedure. This article goal is to help them.
Suppose you have stored procedure like this example
If you go to model browser right click on model as
You will see the structure of Model as bellow snapshot which is created from database using Database First approach:
You can see there have three configuration for store procedure GetCountries.
In Stored Procedure/ Functions section stored procedure schema is defined. If you see the Xml of Model1.edmx then you will see the structure as follows
But there have no mapping with complex type GetCountries_Result.
The mapping is defined in Function Imports Section. Lets see it’s Xml structure
You can see here function name in first Xml section is mapped with Complex Type. There also has properties of ComplexType mapped with Column names of StoredProcedure.
And Complex Type GetCountries_Result is also defined in Complex Type section.
You now need anoter column “Name” have to be returned. So you have changed your stored procedure as
After that if you try to Update model from Database… then your complex type will not add another property with Name as because your stored procedure definition is not changed. What is changed is Function Import mapping. One column is added which is not mapped with any column of complex type.
So you have to edit your Function Import
Then dialog box will be opened to edit your mapping configuration. When you click on Get Column Information then you can see new column “Name” is there.
After that click Update button to Update complex type also and click OK button to commit your changes.
Now you can see your complex Type GetCountries_Result has new property Name which is actually mapped with Name column in Function Imports mapping
This is how database first model structured is defined and its easy to change column configuration after changing stored procedure.