Working with Entity Data Model and Executing Stored Procedures

In this article we are going to work with the following concepts that EDM support. Previous version of the Entity Framework we were generating a model from database which was already created for us. Also we were able to create an empty model and create the conceptual model from scratch. 

If we were to create a model from scratch first we could not create the database schema from that model and which was required some cases. The new EDM has support such cases. So we has support for the following 

1. Database-first: Creating an EDM from an existing database schema.
2. Model-first: Create an empty model first and then generate the database, mappings, and classes from the defined model.
3. Code-only: Creating Entity Framework using Plain Old CLR Objects (POCO) entities and without an EDMX file.

Note: 

1. These examples are based on the AdventureWorks Data Model which can be downloaded from the following link http://msftdbprodsamples.codeplex.com/releases/view/45907 
2. VSTS 2010 Ultimate Trail Version Downloaded on 1 June 2010.
3. SQL Server Express Trail Version Downloaded on 1 June 2010.

Database-First

We will be using Silverlight Basic Application Template for the examples. So that we can also cover the Silverlight out of the box provided template.

1. Create a Silverlight Project by selecting the Silverlight application template; name the client application as DefaultTemplateSilverlightApplication and DefaultTemplateSilverlightWebApplication as host web application.

2. Add a new item by selecting Ado.NET Entity Data Model to the Server application and name it as AdventureWorksModel. Click on Add.

 

3. Select Generate from Data Model and click next.

 

4. Create a connecting string to the Adventure Works Data Model and click next

 

5. In the screen in the wizard is explorer for data base objects. 

  

6. Select the following objects from the Data Base Explorer, So that we can cover each one and click on finish button.

• Table: HumanResources.Employee
• Views: HumanResources.vEmployee
• Stored procedures: uspGetManagerEmployees

7. Take a look at the one the screen in the previous step. The new feature in the EF 4.0 is "Pluralize or Singularize object names"

In the previous version the Entity Names and the EntitySet names were similar which was confusing to the developers. Now we have option to make difference. By selecting the "Pluralize or singularize generated object name" will make all Entity names as singular and EntitySet name as plural. Also makes the navigation property name singular for each navigation property that returns one entity and make the navigation property name plural for each navigation property that returns more than one entity

8. To confirm the same take look at the properties window of Employee. To get this, from the Model window select the properties of the Employee entity.   

  

9. Now compile the solution and make sure there are errors.

10. One more thing you will be surprised that not finding the stored procedure in the generated model. By default stored procedures will not be shown in the model and also code will not be generated. To get the code generated for SP's we will have to generate the function imports for the stored procedure. Follow the following steps.

1. Go to model and right click on the model. Select and click on the Model Browser.
   
   
   
   
2. Locate the stored procedure and right on the SP and click on the "Add function import". By default Function import name will be SP name, and select the sp name drop down will be pointed to the selected SP from Model Brower. Select the Complex option and click on "Get Column Information" and click on "create new complex type". So that the Complex drop down will have the default name selected. As shown below. 
   
   
   
3. Click on ok and compile the solution.
   
   Now we will have the code generated for the stored procedure in Model as follows in the AdventureWorks.Designer.cs.
   
   One key thing to understand here any entity that we generate should have a key column. Now here in this case we don't have any key column in the SP, we can't have one in the SP. So don't forget to add a [Key] column for any one of the public property. Otherwise VSTS 2010 will raise an error stating the same. Don't think that it is VSTS error or draw back. May be we can expect this by next service pack.
   
   #region ComplexTypes
       [EdmComplexTypeAttribute(NamespaceName="AdventureWorksModel", Name="GetManagerEmployeesResult")]
       [DataContractAttribute(IsReference=true)]
       [Serializable()]
       public partial class GetManagerEmployeesResult : ComplexObject
       {
           #region Factory Method
           public static GetManagerEmployeesResult CreateGetManagerEmployeesResult(global::System.String managerFirstName,
   global::System.String managerLastName)
           {
               GetManagerEmployeesResult getManagerEmployeesResult = new GetManagerEmployeesResult();
               getManagerEmployeesResult.ManagerFirstName = managerFirstName;
               getManagerEmployeesResult.ManagerLastName = managerLastName;
               return getManagerEmployeesResult;
           }
           #endregion
           #region Primitive Properties
           [Key]
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
           [DataMemberAttribute()]
           public Nullable<global::System.Int32> RecursionLevel
           {
               get
               {
                   return _RecursionLevel;
               }
               set
               {
                   OnRecursionLevelChanging(value);
                   ReportPropertyChanging("RecursionLevel");
                   _RecursionLevel = StructuralObject.SetValidValue(value);
                   ReportPropertyChanged("RecursionLevel");
                   OnRecursionLevelChanged();
               }
           }
           private Nullable<global::System.Int32> _RecursionLevel;
           partial void OnRecursionLevelChanging(Nullable<global::System.Int32> value);
           partial void OnRecursionLevelChanged();
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
           [DataMemberAttribute()]
           public Nullable<global::System.Int32> ManagerID
           {
               get
               {
                   return _ManagerID;
               }
               set
               {
                   OnManagerIDChanging(value);
                   ReportPropertyChanging("ManagerID");
                   _ManagerID = StructuralObject.SetValidValue(value);
                   ReportPropertyChanged("ManagerID");
                   OnManagerIDChanged();
               }
           }
           private Nullable<global::System.Int32> _ManagerID;
           partial void OnManagerIDChanging(Nullable<global::System.Int32> value);
           partial void OnManagerIDChanged();
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
           [DataMemberAttribute()]
           public global::System.String ManagerFirstName
           {
               get
               {
                   return _ManagerFirstName;
               }
               set
               {
                   OnManagerFirstNameChanging(value);
                   ReportPropertyChanging("ManagerFirstName");
                   _ManagerFirstName = StructuralObject.SetValidValue(value, false);
                   ReportPropertyChanged("ManagerFirstName");
                   OnManagerFirstNameChanged();
               }
           }
           private global::System.String _ManagerFirstName;
           partial void OnManagerFirstNameChanging(global::System.String value);
           partial void OnManagerFirstNameChanged();
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
           [DataMemberAttribute()]
           public global::System.String ManagerLastName
           {
               get
               {
                   return _ManagerLastName;
               }
               set
               {
                   OnManagerLastNameChanging(value);
                   ReportPropertyChanging("ManagerLastName");
                   _ManagerLastName = StructuralObject.SetValidValue(value, false);
                   ReportPropertyChanged("ManagerLastName");
                   OnManagerLastNameChanged();
               }
           }
           private global::System.String _ManagerLastName;
           partial void OnManagerLastNameChanging(global::System.String value);
           partial void OnManagerLastNameChanged();
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
           [DataMemberAttribute()]
           public Nullable<global::System.Int32> EmployeeID
           {
               get
               {
                   return _EmployeeID;
               }
               set
               {
                   OnEmployeeIDChanging(value);
                   ReportPropertyChanging("EmployeeID");
                   _EmployeeID = StructuralObject.SetValidValue(value);
                   ReportPropertyChanged("EmployeeID");
                   OnEmployeeIDChanged();
               }
           }
           private Nullable<global::System.Int32> _EmployeeID;
           partial void OnEmployeeIDChanging(Nullable<global::System.Int32> value);
           partial void OnEmployeeIDChanged();
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
           [DataMemberAttribute()]
           public global::System.String FirstName
           {
               get
               {
                   return _FirstName;
               }
               set
               {
                   OnFirstNameChanging(value);
                   ReportPropertyChanging("FirstName");
                   _FirstName = StructuralObject.SetValidValue(value, true);
                   ReportPropertyChanged("FirstName");
                   OnFirstNameChanged();
               }
           }
           private global::System.String _FirstName;
           partial void OnFirstNameChanging(global::System.String value);
           partial void OnFirstNameChanged();
           [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
           [DataMemberAttribute()]
           public global::System.String LastName
           {
               get
               {
                   return _LastName;
               }
               set
               {
                   OnLastNameChanging(value);
                   ReportPropertyChanging("LastName");
                   _LastName = StructuralObject.SetValidValue(value, true);
                   ReportPropertyChanged("LastName");
                   OnLastNameChanged();
               }
           }
           private global::System.String _LastName;
           partial void OnLastNameChanging(global::System.String value);
           partial void OnLastNameChanged();
   #endregion
   
   
   

11. In the server project add new Domain service item. And Name it as EmployeeDomainService. Click on add

 

12. Select the check boxes as shown in the diagram. By selecting the Entities check box, code will be generated for the entities. By selecting the Enable editing, the wizard will generate the CRUD operation methods to the entities. By selecting the Generate associated class for metadata, metadata classes will be generated for the entities. 

13. But after all there will not be any code generated for the stored procedure or for the import functions.  Please follow the following steps to manually add the code. Open the EmployeeDomainService.cs and add the following code.

public ObjectResult<GetManagerEmployeesResult> GetEmployeesByManager(int manager)

{

    return this.ObjectContext.GetManagerEmployees(manager);

}

14. Create UI as per the following xaml

     <Grid x:Name="LayoutRoot" Background="White">

        <Grid.ColumnDefinitions>

            <ColumnDefinition></ColumnDefinition>

            <ColumnDefinition></ColumnDefinition>

            <ColumnDefinition></ColumnDefinition>

        </Grid.ColumnDefinitions>

        <Grid.RowDefinitions>

                <RowDefinition Height="26*" />

                <RowDefinition Height="274*" />

        </Grid.RowDefinitions>

        <my:Label x:Name="lblManager" Content="Manager :" Grid.Row="0" Grid.Column="0"  />

        <TextBox x:Name="txtSearch" Grid.Row="0" Grid.Column="1" />

        <Button x:Name="btnSeach" Content="Search" Grid.Row="0" Grid.Column="2" Click="btnSeach_Click" />

        <my1:DataGrid x:Name="dgSearchResults" Grid.Row="1" Grid.ColumnSpan="3" />

    </Grid>

15. Add the following code in the cs file.

EmployeeDomainServices _context = new EmployeeDomainServices();

private void btnSeach_Click(object sender, RoutedEventArgs e)

{

    LoadOperation<GetManagerEmployeesResult> load =

            this._context.Load<GetManagerEmployeesResult>(this._context.GetEmployeesByManagerQuery( int.Parse( txtSearch.Text)));

    dgSearchResults.ItemsSource = load.Entities;

}

16. Compile and run the application