Programming Entity Framework

Originally posted on DataDeveloper.net 

This tutorial demonstrates how to wire up stored procedures from your database to an entity in your Entity Data Model.

This basic tutorial works with three simple stored procedures which I added to the AdventureWorksLT database: InsertCustomer, UpdateCustomer and DeleteCustomer and does not involve related data. See the bottom of the post for the TSQL to create these procedures.

1. Create an Entity Data Model using the Entity Data Model Wizard (for assistance with this step, see the Create Entity Data Model Tutorial)
   
   Note: Be sure to include the stored procedures when selecting which database objects to include in the model.
   
    
2. Right click the Customer entity and choose "Stored Procedure Mapping" from the menu.
   
   
    
3. Here you will see options for mapping an Insert function, an Update function and a Delete function.
   
   (Note: The Stored Procedure Mappings are one of the two pages of the Mapping Details view. The top icon on the left will bring you to the Table Mappings for the entity.) 
    
4. Use the drop down menu for Select Insert Function, choose the InsertCustomer procedure.
    
   
   After selecting the matching stored procedure, you will have the ability to map the required parameters for the stored procedure to the properties of the entity. By default, names that match will be mapped to each other already. The benefit of mapping the stored procedure parameters to the entity properties is that you will not need to write code to feed the correct pieces of data to the stored procedure; Entity Framework will use these mappings to do that task.
    
5. Type NewCustomerID property into the Result Column Bindings and hook it up to the CustomerID property.
   
   The stored procedure returns the new ID for the inserted row. This mapping ensures that the new id is pushed back into the entity that the user is working with.
   
   
    
6. Map the Update and Delete functions. As with the insert function, the tool will automatically map the matching column and property names.

When all of the stored procedures are mapped, the Mapping Details will look like this:

Two important notes about using the function mappings.

1) Function mappings require that you map all three of the funcitons, Insert, Update and Delete. If you only map one or two of the functions, the model will not validate.

2) Entities with Navigation Properties that are Entity References (eg SalesOrderHeader.Customer is an Entity Reference, while SalesOrderHeader.SalesOrderDetails is an Entity Collection), will have special requirements for mapping the foreign keys. In an Insert or Update procedure, this is pretty straight forward, but in a Delete procedure, it may require adding parameters to the procedure tha tyou do not use in the procedure itself, but are there only to satisfy the model's rules. See this blog post for more info: Delete Stored Procs And Navigations In the Entity Data Model

Using the new stored procedures

Now that the stored procedures have been mapped, it is not necessary to call them directly in code. Any time SaveChanges is called, Entity Framework will use your mapped stored procedures for any required inserts, updates and deletes.

TSQL for creating the three stored procs in AdventureWorksLT.

INSERT

USE [AdventureWorksLT]

CREATE Procedure [dbo].[InsertCustomer]
  @NameStyle bit,
           @Title nvarchar(8),
           @FirstName nvarchar(50),
           @MiddleName nvarchar(50),
           @LastName  nvarchar(50),
           @Suffix nvarchar(10),
           @CompanyName nvarchar(128),
           @SalesPerson nvarchar(256),
           @EmailAddress nvarchar(50),
           @Phone  nvarchar(25),
           @PasswordHash varchar(128),
           @PasswordSalt varchar(10),
           @ModifiedDate datetime,
           @Inactive bit
AS
INSERT INTO [AdventureWorksLT].[SalesLT].[Customer]
           ([NameStyle]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[CompanyName]
           ,[SalesPerson]
           ,[EmailAddress]
           ,[Phone]
           ,[PasswordHash]
           ,[PasswordSalt]           
           ,[ModifiedDate]
           ,[Inactive])
     VALUES
           (
 @NameStyle,
           @Title,
           @FirstName ,
           @MiddleName,
           @LastName  ,
           @Suffix ,
           @CompanyName ,
           @SalesPerson ,
           @EmailAddress,
           @Phone,
           @PasswordHash,
           @PasswordSalt,
           @ModifiedDate,
           @Inactive         )
                      
select SCOPE_IDENTITY() as NewCustomerID           

UPDATE

USE [AdventureWorksLT]

CREATE PROCEDURE [dbo].[UpdateCustomer]
@CustomerID int,
  @NameStyle bit,
           @Title nvarchar(8),
           @FirstName nvarchar(50),
           @MiddleName nvarchar(50),
           @LastName  nvarchar(50),
           @Suffix nvarchar(10),
           @CompanyName nvarchar(128),
           @SalesPerson nvarchar(256),
           @EmailAddress nvarchar(50),
           @Phone  nvarchar(25),
           @PasswordHash varchar(128),
           @PasswordSalt varchar(10),
           @ModifiedDate datetime,
           @Inactive bit
AS
UPDATE [AdventureWorksLT].[SalesLT].[Customer]
   SET [NameStyle] = @NameStyle,
      [Title] = @Title, 
      [FirstName] = @FirstName,
      [MiddleName] = @MiddleName, 
      [LastName] = @LastName, 
      [Suffix] = @Suffix, 
      [CompanyName] = @CompanyName,
      [SalesPerson] = @SalesPerson, 
      [EmailAddress] = @EmailAddress,
      [Phone] = @Phone,
      [PasswordHash] = @PasswordHash,
      [PasswordSalt] = @PasswordSalt,
      [ModifiedDate] = @ModifiedDate,
      [Inactive] = @Inactive
 WHERE CustomerID=@CUstomerID

DELETE

USE [AdventureWorksLT]

CREATE PROCEDURE [dbo].[DeleteCustomer]
@CustomerID int
AS

DELETE FROM Customer
 WHERE CustomerID=@CUstomerID