Stored Procedures in the Entity Framework

As an Object Relational Mapping tool, one of the Entity Framework’s key features is that given an Entity Data Model (EDM), it makes data access as simple as accessing properties of a class. Another is that it can automatically generate database commands – for queries as well as database changes – on your behalf by reading your Entity Data Model and supporting metadata about the database schema. For many developers, especially those beginning brand new applications with brand new databases, removing the need to write so much redundant code and work out how to express the database commands is a huge productivity improvement.
However, there are many more developers who are writing applications against legacy databases that are replete with important data logic in views and stored procedures. You certainly want to leverage these for a variety of reasons – corporate mandate, you have the benefit of database gurus in house, or some other good cause –while continuing to gain the many other benefits of the Entity Framework (EF) in your application.
Fortunately, Microsoft is not at all asking you to abandon your stored procedures: you are simply being provided with more options. Store procedures provide consistency and security over the wild-west that is inserting SQL directly into your applications and the Entity Framework is not asking you to embed your SQL expressions into your applications. Instead, you will write LINQ to Entities against your entity classes (or Entity SQL expressions against your EDM) such that the Entity Framework, along with the relevant ADO.NET Data Provider, will generate SQL on the fly. It’s a much safer path and the way the SQL is generated there is a very limited chance of inviting security problems with the ORM. However, if you prefer to use your existing stored procedures, or simply prefer to create stored procedures for Entity Framework to use, this path is completely supported as well.
The Entity Framework allows you to use stored procedures in the Entity Data Model instead of, or in combination with, its automatic command generation. When designing your model, you can:

• specify that EF should use your stored procedures for inserting, updating, or deleting entities.
• create model functions to call query stored procedures that return entities or results that do not match any of the entities in your model.
• provide functions that call actions in the database but do not return data at all, such as performing a sweeping update across rows in a table.
• define a stored procedure within custom functions directly in your model without it existing in the database.
• map modification stored procedures to entities that are based on database views, eliminating EF’s need for direct table access.

In this whitepaper you will learn a variety of ways to leverage your existing (or even new) stored procedures when using an Entity Data Model and the Entity Framework in your .NET applications, regardless of the back-end database.

Creating a Simple Entity Data Model to Work With

The following demos will interact with a modified version of Microsoft’s sample AdventureWorksLT database, called AdventureWorksSuperLT. This database is contained in the code samples for this article (see top of article for downloads).
You can follow these steps to create an Entity Data Model against the database in Visual Studio 2010.

1. Create a new Class Library project called SuperLTModel.
2. Add an existing item to the project and navigate to the folder where you saved the AdventureWorksSuperLT database. Be sure to change the file type selection on the lower right corner of the dialog to Data Files so that you can see the database.
3. Select the AdventureWorksSuperLT database and click the Add button.
   Adding the database to the project in this way will cause Visual Studio to prompt you to create a strongly typed Dataset or an EDM.
4. In the Data Source Configuration Wizard’s Choose a Database Model screen select Entity Data Model to start the Entity Data Model Wizard.
5. The Choose Your Data Connection screen will have the newly added database pre-selected.
6. Rename the Entity Connection settings at the bottom of the screen from the default to AWEntities.
7. The next screen of the wizard will invite you to select Database Objects to be used in your model.
8. Expand Tables and select the following tables to be included:
   Customer and SalesOrderHeader
9. Expand Views and select the vSalesOrderDetail.
10. Expand Stored Procedures and select the following eight stored procedures:
    DeleteCustomer, DeleteOrder, DeleteOrderDetails, InsertOrder, InsertOrderDetails, UpdateCustomer, UpdateOrder and UpdateOrderDetails.
11. Rename the Model Namespace to AWModel.
12. Click Finish.

The Entity Data Model Designer will open up with the following model displayed.

Figure 1
Notice that none of the stored procedures you selected are visible in the model in the designer. The visual model only represents your entities. Currently the stored procedures you selected are described in the Store Schema Description Layer which you can see in the raw XML or in the designer’s Model Browser. Even if the model was aware of the stored procedures, which it isn’t yet, you won’t see evidence on the design surface.
You can right click the background of the model surface and select Model Browser to see that the stored procedures are indeed accounted for.

Figure 2
As you progress through this whitepaper, you will be bringing these stored procedures into the model and then use them in your application when coding against the model and its generated classes.
Before moving on, you’ll need to make a few changes to the model; one is critical, the others are cosmetic:

1. First, make the following entity and property name changes:
   1. Change SalesOrderHeader to Order.
   2. Change vSalesOrderDetail to OrderDetail.
   3. Change the SalesOrderHeaders property in the Customer entity to Orders.
   4. Change the SalesOrderID property in Order to OrderID
   5. Change the SalesOrderID property in OrderDetail to OrderID
   6. Change the SalesOrderDetailID property in OrderDetail to OrderDetailID.
2. In the OrderDetail, right click on the LineTotal property and uncheck EntityKey in the menu. You’ll notice the little key icon next to that property disappears as a result. When mapping to database views, the wizard infers entity keys from all of the non-nullable columns. By unchecking the EntityKey flag, you’ve removed the LineTotal from the EntityKey.
   Because this model uses the SalesOrderDetail view rather than the table, you’ll need to manually create the relationship between that and the Order entity:
3. Right click the Order entity and select Add Association.
4. In the Add Association wizard ensure that the details match Figure 3. Note that the Add foreign key properties checkbox is unchecked. The foreign keys already exist in the OrderDetail entity, so you don’t need to add them again.

Figure 3
Finally you’ll need to define the constraints of this relationship:

5. Right click the new association line between Order and Order Detail and select Properties.
6. In the Properties window, click on the Referential Constraint property which will respond by displaying an ellipses button.
7. Click that button and define the constraint as shown in Figure 4.

Figure 4
When you are finished, the model should look similar to that in Figure 5.

Figure 5
Now that you have the model in place you can begin implementing the stored procedures.

Mapping  Modification Stored Procedures  to Entities

By default, the Entity Framework will generate the database queries and update commands when your code interacts with the model.
For example, if your code queries for a specific Order, edits it, and then calls Entity Framework’s SaveChanges command, the query and the required Update command will be constructed and executed by the Entity Framework. Here’s an example of such code:

var order = context.Orders.FirstOrDefault(o => o.OrderID == 71845);
      order.ShipDate = DateTime.Now;
      context.SaveChanges();

Entity Framework will cause the following commands to be executed in the database, first the SELECT and then the UPDATE:

SELECT TOP (1)
[Extent1].[SalesOrderID] AS [SalesOrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[DueDate] AS [DueDate],
[Extent1].[OnlineOrderFlag] AS [OnlineOrderFlag],
[Extent1].[SalesOrderNumber] AS [SalesOrderNumber],
[Extent1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
[Extent1].[AccountNumber] AS [AccountNumber],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[BillToAddressID] AS [BillToAddressID],
[Extent1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
[Extent1].[SubTotal] AS [SubTotal],
[Extent1].[Comment] AS [Comment],
[Extent1].[ModifiedDate] AS [ModifiedDate],
[Extent1].[ShipDate] AS [ShipDate]
FROM [SalesLT].[SalesOrderHeader] AS [Extent1]
WHERE 71845 = [Extent1].[SalesOrderID]
exec sp_executesql N'update [SalesLT].[SalesOrderHeader]
set [ShipDate] = @0
where ([SalesOrderID] = @1)
select [SalesOrderNumber]
from [SalesLT].[SalesOrderHeader]
where @@ROWCOUNT > 0 and [SalesOrderID] = @1',
N'@0 datetime2(7),@1 int',
@0='2011-02-17 11:22:28.5829448',@1=71845

The second command is actually a batch of two statements, as the Entity Framework ensures that the calculated column, SalesOrderNumber, is returned to the Order instance after the update has been performed.
Now you’ll modify the model so that the Entity Framework will use the UpdateOrder stored procedure defined in the database instead of generating its own command.
You’ll do this by mapping the UpdateOrder stored procedure to the Order entity in the designer. This is referred to as Stored Procedure Mapping or Function Mapping.
When you selected database objects in the EDM Wizard, there were three stored procedures for the Order: InsertOrder, UpdateOrder, and DeleteOrder.
While you can map only a subset ofthese to the Order entity
 there is an important caveat when selectively mapping the functions. The Entity Framework has an all-or-nothing policy. It cannot use stored procedures for some actions and command generation for others on a single entity type. If one of the functions is mapped but not the others, SaveChanges will fail at runtime if one of the unmapped actions is needed. For example if you map the insert and delete but not the update, you will get a runtime exception if any modifications need to be saved for that entity. Therefore the three feasible choices are to map no functions, all three functions, or all of the functions that you intend to use in your application. The recommended best practice, however, is to map all three.
For the sake of learning how to map all three types of stored procedures, you’ll map the InsertOrder, UpdateOrder, and DeleteOrder in the following walkthrough.
Here is the SQL for the InsertOrder stored procedure:

ALTER PROC [dbo].[InsertOrder]
    @OrderDate datetime,
    @DueDate datetime,
    @OnlineOrderFlag flag,
    @PurchaseOrderNumber ordernumber,
    @AccountNumber accountnumber,
    @CustomerID int,
    @BillToAddressID int,
    @CreditCardApprovalCode varchar(15),
    @SubTotal money,
    @Comment nvarchar(MAX),
    @ModifiedDate datetime,
    @ShipDate date
AS
    INSERT INTO [SalesLT].[SalesOrderHeader] ([OrderDate], [DueDate], [OnlineOrderFlag], [PurchaseOrderNumber], AccountNumber], [CustomerID], [BillToAddressID], [CreditCardApprovalCode], [SubTotal], [Comment], [ModifiedDate], [ShipDate])
    SELECT @OrderDate, @DueDate, @OnlineOrderFlag, @PurchaseOrderNumber, @AccountNumber, @CustomerID, @BillToAddressID, @CreditCardApprovalCode, @SubTotal, @Comment, @ModifiedDate, @ShipDate
        SELECT [SalesOrderID]
    FROM   [SalesLT].[SalesOrderHeader]
    WHERE  [SalesOrderID] = SCOPE_IDENTITY()

When the INSERT is completed, the query executes a SELECT to return the primary key value, SalesOrderID, of the new row.
Notice that the SalesOrderNumber is not included in the query. It is a calculated column in the database and therefore can’t be inserted.
Here’s how to map the stored procedures that are listed in the Store schema (as you saw in Figure 2) to the Order entity. It will be helpful to know that in the SSDL section of the EDMX, these stored procedures are defined in <Function> elements.

1. In the designer, right click on the Order entity and select Stored Procedure mapping.
2. Click <Select Insert Function> and then click the drop down arrow that appears.
   This exposes the list of all Functions found in the Store metadata.
3. Select InsertOrder from the list. The designer will do its best job of matching the stored procedure’s parameters with the entity properties using the names. In this case, since all of the property names match the parameter names, it maps every one correctly so you don’t need to make any changes.
   One thing that the designer was not able to map is the newly generated SalesOrderID returned by the stored procedure. This is because the designer is not able to automatically detect the name of the field being returned.
4. Under the Result Column Bindings section, click <Add Result Binding> and enter SalesOrderID. The designer should automatically select the entity key property, OrderID, for this final mapping.

When completed, Figure 6 displays what the mapping should look like.

Figure 6
If you were to rerun the previous code example that queried for an order and updated its ShipDate you would get the following runtime exception because the update function is not mapped.
Cannot find the UpdateFunctionMapping for EntityType 'Order' in the mapping file.
However an insert, as shown in the following code, will work without any problems.

var order = new Order {
          OrderDate = DateTime.Today,
          PurchaseOrderNumber = "ABC",
          CustomerID = 3,
          ModifiedDate = DateTime.Now,
          OnlineOrderFlag = true };
        context.Orders.AddObject(order);
        context.SaveChanges();

Here is the SQL executed in the database:

exec [dbo].[InsertOrder] @OrderDate = '2010-09-24 00:00:00', @DueDate = NULL,
    @OnlineOrderFlag = 1, @SalesOrderNumber = NULL,
    @PurchaseOrderNumber = N'ABC', @AccountNumber = NULL, @CustomerID = 3,
    @BillToAddressID = NULL, @CreditCardApprovalCode = NULL,
    @SubTotal = 0.0000, @Comment = NULL,
    @ModifiedDate = '2010-09-24 14:07:53.5913857', @ShipDate = NULL

Because of the final select in the InsertOrder procedures, the Order object’s OrderID and SalesOrderNumber properties get updated using the values returned from the new row created in the database.
There are some additional mapping rules to be aware of before moving on:

1. You must map every parameter of the stored procedure
2. Parameters can be mapped only to entity properties. You cannot map the parameters to a scalar value or a function. As an example, you cannot map the ModifiedDate parameter to DateTime.Now .

Next up is the UpdateOrder stored procedure which is written as:

ALTER PROC [dbo].[UpdateOrder]
    @SalesOrderID int,
    @OrderDate datetime,
    @DueDate datetime,
    @OnlineOrderFlag flag,
    @PurchaseOrderNumber ordernumber,
    @AccountNumber accountnumber,
    @CustomerID int,
    @BillToAddressID int,
    @CreditCardApprovalCode varchar(15),
    @SubTotal money,
    @Comment nvarchar(MAX),
    @ModifiedDate datetime,
    @ShipDate date
AS
    SET NOCOUNT ON
    UPDATE  [SalesLT].[SalesOrderHeader]
    SET     [OrderDate] = @OrderDate, [DueDate] = @DueDate,
            [OnlineOrderFlag] = @OnlineOrderFlag,
            [PurchaseOrderNumber] = @PurchaseOrderNumber,
            [AccountNumber] = @AccountNumber, [CustomerID] = @CustomerID,
            [BillToAddressID] = @BillToAddressID,
            [CreditCardApprovalCode] = @CreditCardApprovalCode,
            [SubTotal] = @SubTotal, [Comment] = @Comment,
            [ModifiedDate] = @ModifiedDate, [ShipDate] = @ShipDate
    WHERE   [SalesOrderID] = @SalesOrderID

You can map the UpdateOrder function to in the Function Mapping window, just as you did for the insert. Again, as most of the entity property names handily match the parameter names, the mappings will be automatically selected by the wizard with the exception of the SalesOrderID parameter. You’ll need to map that to the OrderID property. There are no return values for the UpdateOrder function so this task is complete.
If you were to run the QueryAndUpdateAnOrder method again, you will find that the stored procedure is executed rather than SQL dynamically generated by EF.
There are two checkbox columns—Use Original Value and Rows Affected Parameter — in the function mapping that you can use with Update functions. You can also use the Rows Affected Parameter checkbox with Delete functions. These settings force EF to perform concurrency checking when updating or deleting with stored procedures. You can see how this works when mapping the UpdateCustomer stored procedure to the Customer entity.
Notice that the Customer entity has a TimeStamp property which maps to a column in the database table that is a rowversion (also known as timestamp) data type. Any time a row in the table is modified, the database automatically updates this field with a new unique binary number.
You don’t need to use a rowversion value to do concurrency checking in Entity Framework. Value-based concurrency control, detects conflicts whenever some or all of the column values have changed. You can select any of the entity properties to be used for this.
Here you can see how the function mapping allows you to take advantage of that when mapping an update stored procedure.

ALTER PROCEDURE UpdateCustomer
    @CustomerID int,
    @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),
    @ModifiedDate datetime,
    @TimeStamp timestamp
AS
    SET NOCOUNT ON
    UPDATE  [AdventureWorksLT].[SalesLT].[Customer]
    SET     [Title] = @Title,
            [FirstName] = @FirstName,
            [MiddleName] = @MiddleName,
            [LastName] = @LastName,
            [Suffix] = @Suffix,
            [CompanyName] = @CompanyName,
            [SalesPerson] = @SalesPerson,
            [EmailAddress] = @EmailAddress,
            [Phone] = @Phone,
            [ModifiedDate] = @ModifiedDate
    WHERE   CustomerID = @CustomerID
            and TimeStamp = @TimeStamp

The stored procedure uses the timestamp value as part of the filter to identify the row. If the row was modified in the database (by another user or process) since it was retrieved, then that timestamp value will have changed and the row will not be found for the update.
Now let’s see how that affects the mapping. In Figure 7, the UpdateCustomer function is mapped to the Customer entity. The Use Original Value checkbox for the TimeStamp parameter/property mapping is checked. That tells the Entity Framework that when constructing the parameter values for the procedure to use the original TimeStamp value rather than the current value. The original value should be the value which originally came from the database. By sending the original TimeStamp, which is then used as part of the WHERE predicate, the command will be able to detect if the row was modified since it was retrieved by EF’s query. In that case, the UPDATE command will cause an OptimisticConcurrencyException to be thrown when SaveChanges is called. You can learn more about EF’s OptimisticConcurrencyException in MSDN topic “Saving Changes and Managing Concurrency” at http://msdn.microsoft.com/en-us/library/bb738618.aspx.

Figure 7
Alternatively, if the UpdateCustomer stored procedure used an OUTPUT parameter to return the @@RowsAffected value, for example a parameter named RowsUpdated, that parameter would appear on the Parameters list and you could check its Rows Affected Parameter checkbox. If the return value of that function is 0, an OptimisticConcurrencyException will be thrown.
Mapping a delete function is fairly straightforward. You must map the values of the parameters to existing entity properties, most likely any of the key properties. You also have the option of instructing EF to use an original value or to evaluate an OUTPUT parameter that is an integer.
The DeleteOrder stored procedure takes only the SalesOrderID as a parameter.

ALTER PROC [dbo].[DeleteOrder]
    @SalesOrderID int
AS
    SET NOCOUNT ON
    DELETE
    FROM   [SalesLT].[SalesOrderHeader]
    WHERE  [SalesOrderID] = @SalesOrderID

Figure 8
The function mapping with the delete function does not allow you to mark “Use Original Values” because EF uses the original values for deletions. The DeleteCustomer stored procedure uses the timestamp in the same way that the UpdateCustomer procedure does, therefore, you will find a TimeStamp parameter in the Parameters list for this function which is mapped in Figure 9.

Figure 9
If you wish to remove a function mapping, you can do so in the Mapping Details window by selecting <Delete> from the specific function’s dropdown list just as you did to choose one of the stored procedures.

Mapping Functions to Entities that Map to Database Views

So far you have mapped modification functions to entities that map directly to database tables. You can do the same for entities that map to database views. This is an interesting feature because it enables you to build models completely from views and stored procedures without exposing the tables for dynamic queries. In an enterprise where database access is limited to views and stored procedures, this means that you can, indeed, use the Entity Framework. Additionally, developers working with poorly designed databases can avoid struggling to map entities to the database tables by creating more convenient views and stored procedures that they can use in their model.
The Entity Framework will only see that you have an entity and is not concerned if it maps to a table or to a view. You can still compose queries over the view and EF will perform relationship management and change tracking as with any other entity. The only difference is that it EF cannot generate commands to update views on its own. It needs stored procedures to be defined and mapped.
Recall that the OrderDetail entity maps to the vSalesOrderDetail database view. You can create stored procedure mappings from the DeleteOrderDetails, InsertOrderDetails and UpdateOrderDetails to the OrderDetail entity if you’d like following the same steps that you’ve learned in this section.

Using Import Functions to Map Stored Procedures

So far you’ve seen how to work with modification stored procedures that map directly to entities in your model. Entity Framework has a feature called Function Import that lets you work with other types of stored procs. You can import procedures that return data, whether that is a set of entities, a set of scalar values, or even a set of results for which you don’t have a matching type in your model. Function Import also lets you map procedures that affect the database even if they don’t return any data at all.
The Function Import wizard lets you create functions that become part of the conceptual model. While you can see these in the Model Browser, you will not see them on the design surface, which, as mentioned earlier, only displays entities and associations. The default code generator will create methods in the ObjectContext that let you call these functions.
There are some more stored procedures to bring into the model’s metadata which you can do using the Update wizard:

1. Right click on the model’s design surface background.
2. Select Update Model From Database.
3. In the Add page of the wizard, expand Stored Procedures.
4. Select the following four procedures: GetCustomerNamesWithSalesOrderDetails, GetOrderDetailsForOrder, GetDetailsModifiedNames and TotalSalesForYear.

As before, the stored procedures are only part of the store schema metadata which you can see in the Model Browser. The conceptual model does not yet know about them. You can change this by using a feature of the model browser as described in the next section.

Importing Stored Procedures that Return Entities

It is possible to return entities from stored procedures and, by default, these will get change-tracked just as entities returned by queries.
Start with the GetOrderDetailsForOrder. This procedure selects entire rows from the SalesOrderDetails table which match a given SalesOrderID. The OrderDetail entity matches the structure of the SalesOrderDetail table exactly. Because of this one-to-one mapping, you can create a function import from this stored procedure that returns an OrderDetail type.

1. Begin by right-clicking the GetOrderDeailsForOrder Stored Procedure in the AWModel.Store setion of the Model Browser.
2. Select Add Function Import… from the menu.
3. The Add Function Import wizard will appear as shown in Figure 10.

Figure 10
Although this maps to the GetOrderDetailsForOrder procedure, you can give any name that you want to the function, such as GetDetailsForOrder, which is a bit simpler.
Because you know that the results of this procedure match the OrderDetail entity exactly, you can specify that in this wizard:

1. Select Entities under Returns a Collection Of.
2. In the Entities’ drop down, select OrderDetail.
3. Click OK.

If you select an entity that does not match the results, you won’t find out until runtime. The designer is not able to perform such a validation at design time.
In the Model Browser, you’ll see that the new Function Import is part of the conceptual model, AWModel, as shown in Figure 11.

Figure 11
Additionally, if you are using the default code generation template, this function will be realized as a method in the generated ObjectContext class (AWEntities in this case). The method has two overloads and is placed in a region identified as Function Imports.

public ObjectResult<OrderDetail> GetDetailsForOrder
 (Nullable<global::System.Int32> orderid)
{
  ObjectParameter orderidParameter;
  if (orderid.HasValue)
  {
    orderidParameter = new ObjectParameter("orderid", orderid);
  }
  else
  {
    orderidParameter = new ObjectParameter("orderid", typeof(global::System.Int32));
  }
  return base.ExecuteFunction<OrderDetail>("GetDetailsForOrder", orderidParameter);
}
public ObjectResult<OrderDetail> GetDetailsForOrder(Nullable<global::System.Int32>
 orderid, MergeOption mergeOption)
{
  ObjectParameter orderidParameter;
  if (orderid.HasValue)
  {
    orderidParameter = new ObjectParameter("orderid", orderid);
  }
  else
  {
    orderidParameter = new ObjectParameter("orderid", typeof(global::System.Int32));
  }
  return base.ExecuteFunction<OrderDetail>("GetDetailsForOrder", mergeOption,
                                           orderidParameter);
}

The first method takes the expected orderid parameter and returns an ObjectResult of OrderDetail types. The second contains an additional parameter allowing you to specify which merging strategy to use. As with queries, the default is AppendOnly, which will add to the context any entities returned from the database that are not already being tracked and not bother materializing any duplicates. (See MSDN topic “Identity Resolution, State Management, and Change Tracking” at http://msdn.microsoft.com/en-us/library/bb896269.aspx for more information on MergeOptions).
The ObjectResult is an enumerable collection class, but it is a forward-only collection so once it has been enumerated, you cannot enumerate it again. For example, if you call ToList on the result, e.g., GetDetailsForOrder(3).ToList(), then you cannot subsequently provoke another enumeration by calling ToList again, binding the results to a control or executing a foreach over the results.
You can call the method through the context as follows:

var context = new AWEntities();
ObjectResult<OrderDetail> orderDetailEnumerable = context.GetDetailsForOrder(71796);
List<OrderDetail> details = orderDetailEnumerable.ToList();

However, you’ll get an exception stating that the DataReader (the streamed results from the stored procedure) does not match the OrderDetail entity. The message will provide details explaining that there is a problem with the OrderID property.
Remember that when you created the model, you simplified some of the names of the properties. However the function import depends on the schema of the results matching the entity exactly. The property names, the types and the order of the types in the results must line up perfect with the entity or the materialization of the entity objects will fail. The stored procedure does not know about the property name modifications. Nor can you specify a mapping. This mapping is performed internally. So you’ll either need to set the property names back to match the table column names or modify the stored procedure. Most likely, a developer will prefer not to change their domain model to comply with the database, but modifying the stored procedure is not an option. Therefore the best solution may be to create a new stored procedure.
That procedure already exists in the database as GetDetailsModifiedNames. You can delete the FunctionImport and the GetOrderDetailsForOrder stored procedure directly in the Model Browser and then re-run the Update Model from Database Wizard, adding in the GetDetailsModifiedNames procedure.
Now you can create the Function Import for the new procedure. Be sure to name the function, GetDetailsForOrder as you did previously.
You should now be able to successfully run the example code.
By default, these entities returned by the Function Import will be change tracked and their changes can be persisted to the database with SaveChanges.
Note that the ADO.NET Self-Tracking Entity Generator template and Microsoft’s ADO.NET POCO Entity Generator template will also create this method in the generated class.

Importing Stored Procedures that Return Scalar Values

Now let’s take a look at another type of stored procedure – those that return scalar values. The TotalSalesForYear method returns a single currency value. If you return to Figure 10, you’ll see that “Scalars” is a return type option in the Function Import wizard.

1. Following the steps for importing the GetOrderDetailForOrder procedure, import the TotalSalesForYear procedure.
2. Change the Function Import Name to GetTotalSalesForYear
3. Select Returns a Collection Of Scalars
4. In the Scalars drop down, select Decimal.

Tip: If you are unsure of the Scalar type, you can click the Get Column Information button which will execute the stored procedure and display the resulting schema in the grid below the button.

5. Click OK to complete the import.

The method generated for this procedure returns an ObjectResult of nullable Decimal types. Notice that there is no overload. Since the method does not return entities, there is no possibility of change tracking and therefore no merging options need to be used.

public ObjectResult<Nullable<global::System.Decimal>>
 GetTotalSalesForYear(global::System.String fiscalyear)
{
  ObjectParameter fiscalyearParameter;
  if (fiscalyear != null)
  {
    fiscalyearParameter = new ObjectParameter("fiscalyear", fiscalyear);
  }
  else
  {
    fiscalyearParameter = new ObjectParameter("fiscalyear",
                                              typeof(global::System.String));
  }
  return base.ExecuteFunction<Nullable<global::System.Decimal>>
   ("GetTotalSalesForYear", fiscalyearParameter);
}

In this case, the function will only return a single item in the ObjectResult, the sum of all sales for the given year.

var context = new AWEntities();
decimal? TotalSales = context.GetTotalSalesForYear("2004").FirstOrDefault();
Result: 897259.5138

Importing Stored Procedures that Return Types Other than Entities

Often, your stored procedure will return data in a schema that is completely new and won’t match any of your entities. The GetCustomerNamesWithSalesOrderTotals procedure is an example of this.

ALTER Procedure GetCustomerNamesWithSalesOrderTotals
AS
SELECT  Min(FirstName) as FirstName,
        MIN(LastName) as LastName,
        Min(CompanyName) as CompanyName,
        Count(SalesOrderHeader.SalesOrderID) as OrderCount,
        SUM(SalesLT.SalesOrderHeader.SubTotal) as OrderTotal
FROM    SalesLT.Customer
        INNER JOIN SalesLT.SalesOrderHeader
        ON SalesLT.Customer.CustomerID = SalesLT.SalesOrderHeader.CustomerID
GROUP by SalesLT.Customer.CustomerID

It returns FirstName, LastName, CompanyName, OrderCount, and OrderTotal. The Entity Framework tools allow you to create a new type at design time so that you can create functions from this type of procedure.
The Entity Data Model supports a type called a ComplexType. A ComplexType is like an entity except for an important distinction: it does not have an EntityKey and therefore cannot be change-tracked or updated. There are a variety of ways to use ComplexTypes in your model and in your application. Here you will focus on using ComplexTypes to return types that are neither entities nor scalars.

1. From the Model Browser, start the Function Import Wizard for the GetCustomerNamesWithSalesOrderTotals function.
2. Change the Function Import Name to GetCustomerOrderSummary.
3. Click the Get Column Information button. This will cause the designer to execute the stored procedure so that it can read the schema of the result set. The schema will be displayed in the grid below the button.
4. Click the Create New Complex Type button below the grid.
   As a result, the Complex Collection option will be selected and a default name which is the Function Import Name plus “_Result” will be entered as the selected type.
5. Rename the new complex type in the text box to CustomerOrderSummary.
6. Click the OK button.

The dialog should now look like Figure 12.

Figure 12
In addition to the new function, you can see the new Complex Type in the Model Browser as shown in Figure 13.

Figure 13
One of the great advantages of returning a Complex Type rather than an Anonymous Type from a LINQ projection, for example, is that you now have a strongly-typed class to work with.
Here is the method that the code generator created in the AWModel class:

public ObjectResult<CustomerOrderSummary> GetCustomerOrderSummary()
{
  return base.ExecuteFunction<CustomerOrderSummary>("GetCustomerOrderSummary");
}

It returns an ObjectResult of your new type, CustomerOrderSummary. Here’s an example of calling that method and extracting some of the properties from the resulting rows:

var context = new AWEntities();
foreach (CustomerOrderSummary cos in context.GetCustomerOrderSummary())
{
  Console.WriteLine("{0} # Orders: {1} Total Sales: {2:C}",
    cos.CompanyName, cos.OrderCount, cos.OrderTotal);
}
Subset of Results:
Trailblazing Sports # Orders: 3 Total Sales: $46,237.01
Paints and Solvents Company # Orders: 1 Total Sales: $12,685.89
Channel Outlet # Orders: 1 Total Sales: $550.39
Thrifty Parts and Sales # Orders: 2 Total Sales: $1,836.49

Function Imports for Modification Stored Procedures

The stored procedures imported thus far are ones which only return data, but they don’t necessarily need to be. The Entity Framework doesn’t care what the procedure is doing in the database: it is only concerned with input parameters and results, if any. Therefore you can also import stored procedures that make modifications to the database. These procedures may also return results — whether those are entities, scalars, complex types, or as you can see with the last of the return options in the Add Function Import wizard, procedures that return nothing at all.
An example of such a procedure is one that deletes rows from one or more tables. With EF, the ObjectContext needs to be aware of an entity before it can delete it. That means either retrieving it first from the database or creating a fake entity to add to the context so that you can delete it. There are often scenarios where you simply want to provide an available identity value and have the row deleted. There are already a number of DELETE stored procedures in the database which you can use in Function Mapping (mapping to the entities as you did earlier) or Import Functions. The DeleteOrderDetails is one such procedure that you have already brought into the metadata. You may have even mapped it to the OrderDetail entity already. Now you can also use it as a function.
The SalesOrderDetail table does not have a column such as TimeStamp used as concurrency. It takes two parameters and returns no results.

ALTER PROC [dbo].[DeleteOrderDetails] 
    @SalesOrderID int,
    @SalesOrderDetailID int
AS
    DELETE
    FROM   [SalesLT].[SalesOrderDetail]
    WHERE  [SalesOrderID] = @SalesOrderID
           AND [SalesOrderDetailID] = @SalesOrderDetailID

Using the steps you’ve learned for Function Import, create a Function Import from the DeleteOrderDetails function that is listed in the Model Browser. This time, however, you will select None as the Returns Collection of Type option. The resulting AWEntities method is a bit longer since it performs validation on the input parameter values:

public int DeleteOrderDetails(Nullable<global::System.Int32> salesOrderID, 
Nullable<global::System.Int32> salesOrderDetailID)
{
  ObjectParameter salesOrderIDParameter;
  if (salesOrderID.HasValue)
  {
    salesOrderIDParameter = new ObjectParameter("SalesOrderID", salesOrderID);
  }
  else
  {
    salesOrderIDParameter = new ObjectParameter("SalesOrderID", typeof(global::System.Int32));
  }
  ObjectParameter salesOrderDetailIDParameter;
  if (salesOrderDetailID.HasValue)
  {
    salesOrderDetailIDParameter = new ObjectParameter("SalesOrderDetailID", salesOrderDetailID);
  }
  else
  {
    salesOrderDetailIDParameter = new ObjectParameter("SalesOrderDetailID", typeof(global::System.Int32));
  }
  return base.ExecuteFunction("DeleteOrderDetails", salesOrderIDParameter, salesOrderDetailIDParameter);
}

Notice that even though you selected None as the result, the generated method returns an integer which, according to the ExecuteFunction documentation reports the rows affected. The stored procedure returns the affected records by default because I have not included SET NOCOUNT ON. If NOCOUNT was on, then result of the function would always be -1. However, because this procedure does return the count, the result reports the number of affected rows. That’s useful information that you can use to determine success or failure of this particular stored procedure.
If you are using the Self-Tracking Entities or POCO templates to generate your code, be aware that these two templates will not generate the context method for Function Imports that do not return results.

Comparing the Use of Query Stored Procedures to Views in an Entity Data Model

You’ve seen now that you can return entities or new types from stored procedures as well as map entities to views. There are some noteworthy differences between using views or stored procedures in an Entity Data Model.
When mapping entities from views, you get strongly-typed classes whose changes can be tracked by the context and persisted back to the database. Because the store schema indicates that the data comes from a view not a table, EF will not automatically build modification commands and you need to use stored procedures to perform inserts, updates, and deletes.
When you create a function import for a stored procedure that returns entities, the context will also track changes to these entities. If the entity is mapped to a table, EF can use either mechanism (dynamically built SQL or your mapped stored procedures) for updates. If the entity is mapped to a view, again you must use stored procedures to persist back to the database.
It is also possible that your stored procedure does not return an entity. Only entities can be tracked by the context. Changes to objects that are complex types cannot be persisted to the database when your code calls SaveChanges. If you want the changes to be persisted, then you would be better off creating a view and an entity that maps to the view.
What about comparing view entities to stored procedures that return entities? They both return entities which can be change tracked and persisted to the database, but there are still two important distinctions.
The first is that a function cannot be used in a LINQ to Entities query. As you have seen, these functions return IEnumerables, not IQueryables. What does this mean? Let’s revisit the earlier code that called the GetDetailsForOrder function:

var context = new AWEntities();
ObjectResult<OrderDetail> orderDetailEnumerable = context.GetDetailsForOrder(71796);
List<OrderDetail> details = orderDetailEnumerable.ToList();

The call to context.GetDetailsForOrder(71796) is a function that is executed immediately and returns a set or results.
You could write a query against the function, for example:

OrderDetail detail = context.GetDetailsForOrder(71796).FirstOrDefault();

However what will happen is that FirstOrDefault will be called on the results of the GetDetailsForOrder method. This means that the entire set of results will be returned to memory and then the first one of those will be returned to the detail variable. You may have expected that the FirstOrDefault part of the query would be executed in the database and only the single item would be returned from memory. But that is not possible because stored procedures are not composable. You cannot, in other words, use a stored procedure within a database query. This still may be the right option for your business logic, but beware that if you compose a LINQ to Objects query over the function, there will be no warning from the .NET framework compiler to alert you to which part of the query will be executed on the server and which will be executed on the in-memory objects. It’s important, therefore, to have an understanding of how this works. Query profiling can also aid you in detecting these types of problems.
Views, on the other hand, are composable so you can add any of the LINQ to Entities methods to it which will be taken into account when the store query is created – filters, sorting, projection, set methods, etc. And because they are entities, you can build up a query against them and then execute the query when the time is right.
Depending on your scenario, you may prefer a queryable, composable entity mapped to a view or you may choose an explicit, executable function that calls a stored procedure. The choice is yours.
If that stored procedure does not return results that map to an entity, but you prefer to use a view, you can create a view in the database that returns results in the same shape as the stored procedure results.
The GetCustomerNamesWithSalesOrderTotals stored procedure is an easy candidate for a view because it does not take or use any parameters:

SELECT  Min(FirstName) as FirstName,
        MIN(LastName) as LastName,
        Min(CompanyName) as CompanyName,
        Count(SalesOrderHeader.SalesOrderID) as OrderCount,
        SUM(SalesLT.SalesOrderHeader.SubTotal) as OrderTotal
FROM    SalesLT.Customer
        INNER JOIN SalesLT.SalesOrderHeader ON SalesLT.Customer.CustomerID = SalesLT.SalesOrderHeader.CustomerID
GROUP by SalesLT.Customer.CustomerID

Creating a view from this query gives you the ability to filter (perform other query methods) the results that you don’t otherwise have with the procedure.
Alternatively, the TotalSalesForYear, does take a parameter — a string representing a year, e.g. 2009.

SELECT     SUM(SubTotal) AS TotalSales
FROM         SalesLT.SalesOrderHeader
WHERE     YEAR(OrderDate) = @fiscalyear

Without the parameter, you would have to drop the filter and the view would lose much of it’s meaning.

SELECT     SUM(SubTotal) AS TotalSales
FROM         SalesLT.SalesOrderHeader

It would simply be a TotalSales view, returning a sum of sales for every single SalesOrderHeader in the database. And since it returns only a single column (and only a single item in that column) there will be no entity properties with which to build queries.
This particular need is best fulfilled with a stored procedure.

Defining Functions in the Model’s Metadata

There are times when a particular database stored procedure would be a great convenience but it does not exist in your database nor is it possible to add it. Consider that the store schema (SSDL) portion of the model’s metadata contains descriptions of what is in the database. In the case of a database stored procedure, for example, the GetDetailsModifiedNames, which you worked with in the previous section, it is specified in the SSDL as follows:

<Function Name="GetDetailsModifiedNames" Aggregate="false" BuiltIn="false"
          NiladicFunction="false" IsComposable="false"
          ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="orderid" Type="int" Mode="In" />
</Function>

You can learn more about the various attributes of the function in the MSDN Documentation topic: Function Element (SSDL) ( http://msdn.microsoft.com/en-us/library/bb738614.aspx.
But there is more to the function element than what is being used in this case. There is another element within Function called CommandText where you can supply the exact SQL that would otherwise be part of a stored procedure. With that, you can add your own Function elements to the SSDL and define the procedure using the CommandText element. For example, here’s
a simple procedure that returns results based on an incoming parameter.

<Function Name="ProductsFromName" IsComposable="false">
  <CommandText>
    SELECT     ProductNumber, Name, ListPrice, ProductID
    FROM         SalesLT.Product
    where Name like '%' + @NameContains + '%';
  </CommandText>
  <Parameter Name="NameContains" Type="nvarchar" Mode="In"/>
</Function>

Because this is in the SSDL, as the  Entity Framework considers it part of the database. It won’t know the difference.
There are two important caveats to be aware of. The first is that manual modifications to the SSDL get overwritten any time you use the Update Model from Database feature of the designer. That designer function recreates the SSDL from the database schema. Visual Studio 2010has no workaround for this. You may want to keep track of any manual SSDL modifications so that if they do get overwritten, you can replace them. I do this simply by copying my modifications into a text file that is part of the project along with some notes to remember the placement of the modifications. The second caveat is that the “Get Column Information” feature of the Function Import wizard is unable to detect schema from stored procedures defined in the SSDL. Instead, you will need to determine the schema yourself and manually create the complex type in the Model Browser. Then in the Function Import wizard, you will be able to choose the complex type from the drop down list of available complex types. You can learn more about creating Complex Types in the Model Browser in the MSDN topic, How to: Create and Modify Complex Types (Entity Data Model Tools), at http://msdn.microsoft.com/en-us/library/dd456820.aspx.
For this example, a new complex type called ShortProduct was created as shown in Figure 14.

Figure 14
You can specify the attributes of the properties in their Properties window, for example, that ListPrice is a Double and Name is a String.
As a result of creating the Function using the Function Import wizard, the code generator will create a new method in the AWEntities class:

public ObjectResult<ShortProduct> GetProductsFromName(global::System.String nameContains)
{
  ObjectParameter nameContainsParameter;
  if (nameContains != null)
  {
    nameContainsParameter = new ObjectParameter("NameContains", nameContains);
  }
  else
  {
    nameContainsParameter = new ObjectParameter("NameContains",
     typeof(global::System.String));
  }
  return base.ExecuteFunction<ShortProduct>("GetProductsFromName",
   nameContainsParameter);
}

You can then call this method in your code to get a list of ShortProduct objects:

var context = new AWEntities();
List<ShortProduct> products = context.GetProductsFromName("Wheel").ToList();

Function Imports and Output Parameters

You can create Functions from stored procedures that contain output parameters whether your procedure returns entities, scalars or complex types or those designed only to return output parameter(s) but no result sets. Following is an example of a stored procedure that returns projected results and a single output parameter.
Here’s an example of a stored procedure with an output parameter:

ALTER PROCEDURE [dbo].[GetCountByLastName] (
    @LastName NVARCHAR(50),
    @LastNameCount INT output )
AS
SELECT FirstName, CompanyName
FROM Customers
Where LastName=@LastName
SET @LastNameCount= @@ROWCOUNT

After bringing this into the model using the designer wizard, the resulting Function marks the Mode of the output parameter as InOut:

<Function Name="GetCountByLastName" Aggregate="false" BuiltIn="false"
         NiladicFunction="false" IsComposable="false"
         ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="LastName" Type="nvarchar" Mode="In" />
  <Parameter Name="LastNameCount" Type="int" Mode="InOut" />
</Function>

After using the Function Import Wizard for this procedure along with the complex type, FirstNameAndCompany, the code generator created the following method in the AWEntities class:

public ObjectResult<FirstNameAndCompany> GetCountByLastName 
   (global::System.String lastName, ObjectParameter lastNameCount)
{
  ObjectParameter lastNameParameter;
  if (lastName != null)
  {
    lastNameParameter = new ObjectParameter("LastName", lastName);
  }
  else
  {
    lastNameParameter = new ObjectParameter("LastName", typeof(global::System.String));
  }
  return base.ExecuteFunction<FirstNameAndCompany>
   ("GetCountByLastName", lastNameParameter, lastNameCount);
}

Notice the second parameter is an ObjectParameter. Here’s how you can call the method from your code:

var outputParam = new ObjectParameter("lastNameCount", new Int32());
      var results = context.GetCountByLastName("Adams", outputParam);

The value of the parameter is in ObjectParameter.Value. But parameters are returned from the database at the end of the stream, after any other results, so you can’t access any object parameters until after the result set has streamed to the end. Here is code that assures the results are streamed to the end and then grabs the value of the parameter and at the same time, casting it to an integer.

var resultsList = results.ToList();//push through results
var outputValue=(int)outputParam.Value);

The Get Column Information feature is unable to obtain the schema of stored procedures with output parameters. As you did for the custom stored procedure in the previous section, then, you will have to manually create the Complex Type in the Model Wizard before running the Function Import wizard. Then you can select that Complex Type when creating the Function Import.

Working with Stored Procedures that Return Multiple Resultsets

The Entity Framework does not directly support stored procedures that return multiple resultsets,  though it is possible to get this behavior in EF4 by using the Translate method and ExecuteStoreReader. However, the Entity Framework team has provided sample utilities, the EFExtensions, on the MSDN Code Gallery that will enable you to consume multiple resultsets. There are other features provided by the EFExtensions, such as renaming columns (to solve a problem which was discussed earlier in this whitepaper). To learn more and download the extensions, visit http://code.msdn.microsoft.com/EFExtensions. Note that the extensions and other samples provided on the Code Gallery, while very useful, are not supported by Microsoft.

Summary

Although command generation is one of Entity Framework’s prominent features, this doesn’t prevent you from leveraging database stored procedures. You’ve now seen that there are a number of ways that you can benefit from an Entity Data Model and EF’s capabilities while using your own stored procedures. You can map modification stored procedures to entities so that EF will automatically use those any inserts, updates and deletes that it needs to perform in the database. You can also create functions from stored procedures, whether those are to query data or make changes to the database and then call them directly in your application code. You’ve also seen how you can combine the EDM’s ability to use database views and stored procedures to completely circumvent EF’s ability to access database tables directly.
With the combination of Entity Framework’s command generation capabilities and it’s broad support for using database stored procedure, you can choose the features that best suit your needs when designing Entity Data Models and planning how to use EF in your applications.