Creating an ADO.NET Entity Framework Entity Data Model

Create a new Class Library Project

Select New Project from File Menu option
Select your preferred project base: e.g. Visual Basic or C#
Select Class Library
Change the name of the application in the Name text box to DataDevEDM
Click OK

Add an Entity Data Model Item

In Solution Explorer, right click the project name.
Click Add on the context menu that opens.
Click New Item.
In the Add New Item dialog, click ADO.NET Entity Data Model.
Change the name from Model1.edmx to AWModel.edmx
addnewitem
Click the Add button
In the Entity Data Model Wizard click Generate from Database, then click the Next button

Create a database connection for the Entity Data Model

If you have a database connection already created in Visual Studio, skip to Define Contents of an Entity Data Model

On the Choose Your Data Connection page of the wizard, click the New Connection button.
At the top of the connection properties window, click the Change button next to the Data Source
clip_image004

Select Microsoft SQL Server Database File from the Data source list, then click OK
Click the Browse button to the right of Database file name
Browse to the folder where you have saved the AdventureWorksLT database*.
Select AdventureWorksLT.mdf then click Open.
clip_image006

At the bottom of the Connection Properties window, click OK

Define the contents of an Entity Data Model

Select the database connection from the drop down.
At the bottom of the Entity Data Model Wizard window, change the value of Entity connection settings to AWEntities then click Next.
clip_image008

On the next page, change the Model Namespace to AWModel.
Check Tables, Views and Stored Procedures.
This will include all of the items. You can expand these nodes to select only the specific tables, views and stored procedures that you want.
clip_image010

Click Finish.
Note that in the solution explorer, a new file, AWModel.edmx has been created.
explorer

View the model

Double click the AWModel.emx file to open the model in the Entity Data Model designer.

model
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.
sproc1

Right click the Customer entity and choose "Stored Procedure Mapping" from the menu.

sproc2

Here you will see options for mapping an Insert function, an Update function and a Delete function.
sproc4
(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.)

Use the drop down menu for Select Insert Function, choose the InsertCustomer procedure.
sproc5

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.

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.

sproc6

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:

sproc7



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