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


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.

Tips: Sorting/Ordering a Collection with Nested/Child Collection value

 

Problem:

Suppose you have a collection and inside that collection you have a child collection. Here is a example of such relation

   1: public class Vahicle

   2:     {

   3:         public int VahicleId { get; set; }

   4:         public string ValicleName { get; set; }

   5:         public List<Reminder> Reminders { get; set; }

   6:     }

   7:  

   8:     public class Reminder

   9:     {

  10:         public int ReminderId { get; set; }

  11:         public string ReminderType { get; set; }

  12:     }

Here you can see Reminder is a nested collection inside Vahicle. You can have  a collection of Vahicles and you may want to display Vahicles shorting by ReminderType.

 

Solution:

To solve this problem I have converted the collection to flat list where there have a property ReminderType so that when I do order by using ReminderType. The total collection will be sorted. SelectMany convert nested collection to flat collection. So I used SelectMany(). to make a flat collection with ReminderType.

   1: var orderedVahicles = vahicles.SelectMany(v => v.Reminders

   2:                                                           .Select(reminder => new { Vahicle = v, reminder.ReminderType })

   3:                                                      ).OrderBy(v => v.ReminderType)

   4:                                                      .Select(v => v.Vahicle).Distinct();

Here is the code Line 1-2 I converted Vahicle nested Collection into a collection where Vahicle collection is a property and also ReminderType is a property using SelectMany(). After Converting the collection I have done orderby with ReminderType and vahicle is also sorted Line 3. As I only need sorted Vahicle collection so I selected only Vahicle property from anonymous object Line 4.

Problem could be more complex  where you may need to select object based on certain criteria and also collection can be nested 3-4 levels or more. You can do another SelectMany if there have another nested collection.