Migrating to SQL Server Stored Procedures with the EDM Designer December CTP 2

It's a reasonably safe bet that all Entity Framework (EF) and LINQ to SQL projects start with an existing database as the persistence store. The ADO.NET Team initially planned to support generating the underlying database schema from domain objects created in the EDM Designer's UI. This goal for v1 was abandoned early in EF's history, although the EDM Wizard retains the option to start with an Empty Model template. LINQ to SQL most commonly employs a one:one table:entity relationship with SQL Server as the data source, but it does support table-per-hierarchy (TPH) inheritance mapping. EF enables entity splitting and joining, as well as table-per-type (TPT) and table-per-concrete-type (TPC) inheritance.

Unless appropriate stored procedures (sprocs) already exist for populating EntitySets and updating Entity instances, most EF and LINQ to SQL developers start their design with dynamic Entity SQL (eSQL) or dynamic T-SQL commands. By default, both EF and LINQ to SQL generate dynamic eSQL or T-SQL for CRUD (create, retrieve, update and delete) operations. Starting in default mode lets you quickly design and test your entities before investing in writing and testing sprocs.

Both EF and LINQ to SQL support migration from dynamic SQL commands to sprocs in their respective designer UIs. Both UIs enable adding value-based optimistic concurrency management for UPDATE sprocs. The most significant difference between the two designers is the EDM Designers requirement for explicit mapping of Associations for INSERT, UPDATE and DELETE sprocs. The EDM Designer also requires you to map the value returned when autogenerating primary keys for INSERT operations from a Result Column Binding property to a Parameter.

The AdoNetEntityFrameworkTools.chm help file for EDM Designer CTP 2 in the \Program Files\ADO.NET Entity Framework Tools Preview folder contains little useful information for migrating from dynamic SQL to sprocs. A search on "stored procedure" returns only five topics which pay lip-service to sprocs; the "How to: Create a Function Import" offers only a cursory three-step description for adding a Function Import.

Update March 29, 2008: Noam Ben-Ami's Stored Procedure Mapping of March 26, 2008 (which appeared in the ADO.NET Team Blog on March 28) provides a script to create simple Product and Order tables as well as INSERT, UPDATE, and DELETE sprocs for the Product table. A sample project exercises the update sprocs.

Test Entities for Migration

The following screen capture illustrates the three entities (Product, Category and Supplier) that I migrated from dynamic SQL to sprocs:

Click image for full-size capture.

Entity names are singularized from plural EntitySet names. Associations and Product Navigation Properties are renamed to reflect their 0..1:* multiplicity. At this point, Products, Categories, and Suppliers are considered to be catalog data (lookup tables), so sprocs that retrieve a single Product instance by ProductID don't have Function Imports in this example.

The Migration Process

The migration process has four phases:

1. Migrating from default eSQL SELECT queries to sprocs for hydrating Entity Sets
2. Migrating from default INSERT, UPDATE and DELETE SQL commands to corresponding custom sprocs
3. Mapping foreign key fields for Associations to the source table's primary key field
4. Adding stored procedures to retrieve EntityReferences and EntityCollections for associations, if you don't retrieve and cache all related Entity instances.

1. Assigning Sprocs as EntitySet Data Sources

Here's the step-by-step procedure to migrate from SQL SELECT queries to sprocs for hydrating EntitySets:

1. Write stored procedures to return the Entity instances or EntitySets you want to hydrate your objects. This example uses sprocs that return all table rows.

2. Right-click the Model Browser pane and choose Update Model From Database to open the EDM Wizard. Clear the Tables, Views, and Stored Procedures check boxes and then expand the Stored Procedures node.

3. Mark the check boxes for the SELECT stored procedures to add and click Finish to import them to the Model Browser's Northwind Model Store | Stored Procedures list and add <Function> groups

4. Right-click each stored procedure and Select Create Function Import to open the New Function Import dialog. Type the Function Import Name you want and select the Return Type from the list, Product for this example:

5. Click OK to add this <FunctionImport> element to the *.csdl file:

<FunctionImport Name="GetAllSuppliers" EntitySet="Suppliers" 
    ReturnType="Collection(Self.Supplier)" />

and the <Function> and <FunctionImportMapping> elements to the *.ssdl file:

<Function Name="usp_GetAllProducts" Aggregate="false" BuiltIn="false" 
    NiladicFunction="false" IsComposable="false" 
    ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" />

<FunctionImportMapping FunctionImportName="GetAllProducts" 
    FunctionName="NorthwindModel.Store.usp_GetAllProducts" />

6. Repeat the process for all other Entities. If you assign an sproc as the data source for an Entity, you must do the same for all other related entities.

7. Save your changes (^s).

2. Moving from INSERT, UPDATE and DELETE SQL Commands to Corresponding Custom Sprocs

INSERT sprocs need a SELECT statement to return the server-assigned foreign-key value, int IDENTITY for this example:

CREATE PROCEDURE usp_InsertProduct (
    @ProductName nvarchar(40),
    @SupplierID int = 1,
    @CategoryID int = 1,
    @QuantityPerUnit nvarchar(20) = 'Insert package data',
    @UnitPrice money = 10.00,
    @UnitsInStock smallint = 0,
    @UnitsOnOrder smallint = 0,
    @ReorderLevel smallint = 0,
    @Discontinued bit = 0)
AS
SET NOCOUNT ON

INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit,
            UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
     VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit,
             @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT SCOPE_IDENTITY() AS ProductID WHERE @@ROWCOUNT > 0;
SET NOCOUNT OFF;

DELETE sprocs need a parameters for each foreign key value:

CREATE PROCEDURE usp_DeleteProduct (
    @ProductID int,
    @SupplierID int,
    @CategoryID int)
AS
SET NOCOUNT ON

DELETE FROM Products 
WHERE ProductID = @ProductID
AND SupplierID = @SupplierID
AND CategoryID = @CategoryID;

SET NOCOUNT OFF;

Note: This important but undocumented requirement is noted in Colin Meek's answer to the Re: CUD with stored procedures - association sets function mapping? question in the ADO.NET Entity Framework and LINQ to Entities (Pre-release) forum.

Entities whose data source is an sproc must have INSERT, UPDATE and DELETE sproc assignments also. To assign INSERT, UPDATE and DELETE sprocs to an Entity, do this:

1. Select the entity, open the Mapping Details pane and click the Map Entity to Functions button.

2. In the Insert row, open the dropdown list and select the INSERT stored procedure, usp_InsertProduct for this example:

3. Map the Result binding by replacing the <Add Result Binding> placeholder with ProductID, opening the Property list box and selecting ProductID (Int32) (look ahead to the next screen capture.)

4. Repeat step 2 for the UPDATE sproc (usp_UpdateProduct) and the DELETE sproc (usp_DeleteSproc). The following <Function> groups add to your *.ssdl file:

<Function Name="usp_InsertProduct" Aggregate="false" BuiltIn="false" 
    NiladicFunction="false" IsComposable="false" 
    ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="ProductName" Type="nvarchar" Mode="In" />
  <Parameter Name="SupplierID" Type="int" Mode="In" />
  <Parameter Name="CategoryID" Type="int" Mode="In" />
  <Parameter Name="QuantityPerUnit" Type="nvarchar" Mode="In" />
  <Parameter Name="UnitPrice" Type="money" Mode="In" />
  <Parameter Name="UnitsInStock" Type="smallint" Mode="In" />
  <Parameter Name="UnitsOnOrder" Type="smallint" Mode="In" />
  <Parameter Name="ReorderLevel" Type="smallint" Mode="In" />
  <Parameter Name="Discontinued" Type="bit" Mode="In" />
</Function>
<Function Name="usp_UpdateProduct" Aggregate="false" BuiltIn="false" 
    NiladicFunction="false" IsComposable="false" 
    ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="ProductID" Type="int" Mode="In" />
  <Parameter Name="ProductName" Type="nvarchar" Mode="In" />
  <Parameter Name="SupplierID" Type="int" Mode="In" />
  <Parameter Name="CategoryID" Type="int" Mode="In" />
  <Parameter Name="QuantityPerUnit" Type="nvarchar" Mode="In" />
  <Parameter Name="UnitPrice" Type="money" Mode="In" />
  <Parameter Name="UnitsInStock" Type="smallint" Mode="In" />
  <Parameter Name="UnitsOnOrder" Type="smallint" Mode="In" />
  <Parameter Name="ReorderLevel" Type="smallint" Mode="In" />
  <Parameter Name="Discontinued" Type="bit" Mode="In" />
</Function>
<Function Name="usp_DeleteProduct" Aggregate="false" BuiltIn="false" 
    NiladicFunction="false" IsComposable="false" 
    ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="ProductID" Type="int" Mode="In" />
  <Parameter Name="SupplierID" Type="int" Mode="In" />
  <Parameter Name="CategoryID" Type="int" Mode="In" />
</Function>

Three similar <ModificationFunctionMapping> groups add to your *.msl file. (A sample entry appears in the next section.)

5. Repeat steps 1 through 4 for the other entities in your model (Categories and Suppliers for this example.

6. Save your changes (^s)

3. Mapping Foreign Key Fields for Associations to the Source Table's Primary-Key Field

The final step is to map primary-key fields to the empty Property cells of foreign-key fields for Associations:

1. Open the field list for the first foreign-key field of the INSERT stored procedure (SupplierID (int)) for this example), and select the corresponding primary-key field (Suppliers.SupplierID).

2. Repeat step 1 for additional foreign-key fields, if any (CategoryID, Categories.CategoryID).

3. Fix a bug in the CTP 2 version of the EDM Designer: Open the *.edmx file in the XML Editor, move to the <ModificationFunctionMapping> section of the *.ssdl file, and move the <ResultBinding> element to the end of the group, as shown here:

<InsertFunction FunctionName="NorthwindModel.Store.usp_InsertProduct">
  <ScalarProperty Name="Discontinued" ParameterName="Discontinued" />
  <ScalarProperty Name="ReorderLevel" ParameterName="ReorderLevel" />
  <ScalarProperty Name="UnitsOnOrder" ParameterName="UnitsOnOrder" />
  <ScalarProperty Name="UnitsInStock" ParameterName="UnitsInStock" />
  <ScalarProperty Name="UnitPrice" ParameterName="UnitPrice" />
  <ScalarProperty Name="QuantityPerUnit" ParameterName="QuantityPerUnit" />
  <ScalarProperty Name="ProductName" ParameterName="ProductName" />
  <AssociationEnd AssociationSet="Products_Suppliers" From="Products" To="Suppliers">
    <ScalarProperty Name="SupplierID" ParameterName="SupplierID" />
  </AssociationEnd>
  <AssociationEnd AssociationSet="Products_Categories" From="Products" To="Categories">
    <ScalarProperty Name="CategoryID" ParameterName="CategoryID" />
  </AssociationEnd>
  <ResultBinding Name="ProductID" ColumnName="ProductID" />
</InsertFunction>

For unknown reasons, the <ResultBinding> element must be the last in the group and the Designer places it above the first <AssociationEnd> group. Until you make this change in the XML Editor, you might not be able to reopen the *.edmx file in the designer.

Note: The preceding bug is described by Craig Lee's reply to my Strange Behavior Changing an EntitySet's Data Source from an Entity SQL Query to a Stored Proc message in the ADO.NET Entity Framework and LINQ to Entities (Pre-release) forum.

4. Repeat steps 1 and 2 for the UPDATE and DELETE functions. (These groups don't have the preceding bug.) Your Mapping Details pane appears as shown here:

5. Repeat the preceding steps for any other entities that have parameters for foreign key values, if any. The Supplier and Category entities don't have these parameters.

6. Save your changes (^s).

4. Write and Import Stored Procedures to Populate EntityReferences to and EntityCollections of Related Entities

If you don't cache complete sets of related objects in the client, you must retrieve related objects to populate the target Entity's EntityReference(s), EntityCollection(s) or both.

Note: Colin Meek's answer to the Re: More Questions About SProc Read post in the ADO.NET Entity Framework and LINQ to Entities (Pre-release) forum recommends:

To relate entities that are loaded through stored procedures, you can use the EntityCollection.Attach() and EntityReference.Attach() methods. There is no mechanism however to include the relationship data from a single stored procedure call. For instance, if the product stored procedure returns foreign keys for the category, those foreign keys are not implicitly mapped to the product-category association set.

I suggest defining a GetProductsForCategory or GetCategoryForProduct stored procedure and establishing relationships as follows:

Product p = context.GetProductByName("apple").First();
p.CategoryReference.Attach(context.GetCategoryForProduct(p.ProductID).First());

For example, you need the following two sprocs to populate a Product Entity's Product_Category and Product_Supplier Associations:

CREATE PROCEDURE usp_GetCategoryForProductById (
    @ProductID int)
AS
SET NOCOUNT ON

SELECT * 
FROM Categories AS c JOIN Products AS p
ON p.CategoryID = c.CategoryID 
WHERE p.ProductID = @ProductID;

CREATE PROCEDURE usp_GetSupplierForProductByID (
    @ProductID int)
AS
SET NOCOUNT ON

SELECT * 
FROM Suppliers AS s JOIN Products AS p
ON p.SupplierID = s.SupplierID 
WHERE p.ProductID = @ProductID;

Note: It's surprising that more folks haven't posted questions in the forum or elsewhere about the preceding issues.

A subsequent post will describe programming these new additions.

As Scott Guthrie says, "Hope this helps." It certainly would have helped me when I ran into the misplaced <ResultBinding> bug and the requirement for foreign-key parameters in DELETE stored procedures.

Updated March 29, 2008: Minor edits to introduction and addition of link to Noam Ben-Ami's post.

Technorati tags: Entity Framework, Entity Data Model, EDM, EDMX, EDM Designer, Stored Procedures, FunctionImport, Stored Procedure Mapping, O/RM, O/R Mapping