Saturday, December 29, 2012

How to For Beginner: Entity Framework Database First Updating Complex type of Model after changing Stored Procedure Columns Selection


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
image

If you go to model browser right click on model as
image

You will see the structure of Model as bellow snapshot which is created from database using Database First approach:
image
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
image
But there have no mapping with complex type GetCountries_Result.
The mapping is defined in Function Imports Section. Lets see it’s Xml structure
image
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.
image

You now need anoter column “Name” have to be returned. So you have changed your stored procedure as
image
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
image

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.
image
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

image
This is how database first model structured is defined and its easy to change column configuration after changing stored procedure.

No comments:

Post a Comment