This preview is no longer current.
The features from this release are now included in Entity Framework 5 Beta 2.
Another feature added to the Entity Framework in the next release is support for consuming multiple result sets from a single stored procedure. This scenario is commonly used to reduce the number of database round trips required to compose a single screen. In the past EF would allow the stored procedure to be called but would only return the first result set to the calling code.
This article will walk you through how to implement this new feature in your solution. Please understand that the designer experience is incomplete as of the June 2011 CTP and we expect to ship a fully tooled experience at RTM. As always, we would love to hear your feedback about this and other features we will be shipping in the next release of the Entity Framework.
We will attempt to consume a stored procedure that returns key details from a customer as well as pertinent details from the last ten orders the customer placed. For instance, running the stored procedure for Alfred’s Futterkiste (ALFKI) returns the following two result sets:
CustomerID CompanyName ContactName Phone
---------- ------------------------- -------------------- --------------
ALFKI Alfreds Futterkiste Maria Anders 030-0074321
(1 row(s) affected)
OrderID OrderDate RequiredDate ShippedDate
----------- ----------------------- ----------------------- -----------------------
11011 1998-04-09 00:00:00.000 1998-05-07 00:00:00.000 1998-04-13 00:00:00.000
10952 1998-03-16 00:00:00.000 1998-04-27 00:00:00.000 1998-03-24 00:00:00.000
10835 1998-01-15 00:00:00.000 1998-02-12 00:00:00.000 1998-01-21 00:00:00.000
10702 1997-10-13 00:00:00.000 1997-11-24 00:00:00.000 1997-10-21 00:00:00.000
10692 1997-10-03 00:00:00.000 1997-10-31 00:00:00.000 1997-10-13 00:00:00.000
10643 1997-08-25 00:00:00.000 1997-09-22 00:00:00.000 1997-09-02 00:00:00.000
In this case, the stored procedure returns two result sets, allowing us to construct the screen with only one round trip to the database. Now let’s see how to utilize multiple result sets end-to-end in an Entity Framework application.
If you haven’t done so already, attach the Northwind database to a SQL Server instance. We tested the walkthrough on SQL Server 2008 R2, but most versions of SQL Server should be sufficient for this walkthrough. You can find instructions on how to attach a database here.
The next step to using multiple result sets from a stored procedure is to actually create the stored procedure. Open SQL Server Management Studio and connect to the SQL Server instance where the Northwind database was attached. Execute the following SQL query:
USE DATABASE Northwind
GO
CREATE PROCEDURE [dbo].[CustWithRecentOrders] @CustomerID nchar(5)
AS
-- Get key customer information
SELECT
CustomerID,
CompanyName,
ContactName,
Phone
FROM Customers
WHERE CustomerID = @CustomerID
-- Get recent orders
SELECT TOP 10
OrderID,
OrderDate,
ShippedDate
FROM Orders o
ORDER BY OrderID DESC
To verify that the stored procedure was created properly, try executing the stored procedure for ALFKI, as shown here:
EXEC dbo.CustWithRecentOrders @CustomerID = 'ALFKI'
Verify that two result sets are returned. When two result sets are returned, SQL Server Management Studio will format each of the result sets in its own grid:
For the purpose of this walkthrough, a simple console application will suffice. The walkthrough uses a C# console application named MultipleResultSets. Please note that you will need to set the project target framework to the Microsoft Entity Framework June 2011 CTP as detailed in the CTP introductory post.
Now that we have verified that the stored procedure exists and returns two result sets, we can create a model to use the stored procedure. Please note that you will need to have the June 2011 CTP bits installed in order to complete this walkthrough.
In the Solution Explorer, right-click the name of the project and select Add > New Item. In the Add New Item dialog, select ADO.NET Entity Data Model and name the item Northwind.edmx.
On the first page of the wizard, choose Generate from database and click Next.
Next, configure the data connection to point to the database where you created the stored procedure and click Next.
Check the box for the stored procedure you created and click Next.
As of the June 2011 CTP, the designer experience is incomplete. We expect to ship a fully tooled experience at release, however we will need to manually modify the CSDL and MSL for this walkthrough. (The SSDL does not need to be modified.) Open the model XML by right-clicking on the model in the Solution Explorer and selecting Open With…
</![IF>
Select Automatic Editor Selector (XML).
The conceptual model contains a stub of the stored procedure as well as the ComplexType that was created to contain the results of the first result set. We will need to make two modifications to the CSDL.
First, add another complex type. I copied and pasted CustWithRecentOrders_Result, ensured that the names were unique and that the data types for the columns were compatible with the result sets.
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema Namespace="NorthwindModel"
Alias="Self"
xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
<EntityContainer Name="NorthwindEntities"
annotation:LazyLoadingEnabled="true" >
<FunctionImport
Name="CustWithRecentOrders"
ReturnType="Collection(NorthwindModel.CustWithRecentOrders_Result)">
<Parameter Name="CustomerID" Mode="In" Type="String" />
</FunctionImport>
</EntityContainer>
<!-- Modified ComplextType name for uniformity with new ComplexType -->
<ComplexType Name="CustWithRecentOrders_Result1">
<Property Type="String" Name="CustomerID" Nullable="false" MaxLength="5" />
<Property Type="String" Name="CompanyName" Nullable="false" MaxLength="40" />
<Property Type="String" Name="ContactName" Nullable="true" MaxLength="30" />
<Property Type="String" Name="Phone" Nullable="true" MaxLength="24" />
</ComplexType>
<!-- Added new ComplexType -->
<ComplexType Name="CustWithRecentOrders_Result2">
<Property Type="Int32" Name="OrderID" Nullable="false" />
<Property Type="DateTime" Name="OrderDate" Nullable="false" />
<Property Type="DateTime" Name="RequiredDate" Nullable="false" />
<Property Type="DateTime" Name="ShippedDate" Nullable="false" />
</Schema>
</edmx:ConceptualModels>
When the function is initially created, the ReturnType is an attribute of the FunctionImport element.
In order to use multiple result sets, we need to remove this attribute and add two nested elements to the FunctionImport element. Please note that for the June 2011 CTP the ReturnType elements must precede the Parameter element. After making this change, the CSDL modifications are complete:
<!-- ReturnType attribute removed, child elements added -->
<FunctionImport Name="CustWithRecentOrders">
<ReturnType Type="Collection(NorthwindModel.CustWithRecentOrders_Result1)" />
<ReturnType Type="Collection(NorthwindModel.CustWithRecentOrders_Result2)" />
The MSL must also be modified to reflect the multiple return types. The generated MSL should already contain a stub for the first return type; this stub needs to be duplicated and modified to accurately reflect the second return type. As with the CSDL above, ensure that the names match the CSDL and ensure that the column names were set appropriately. After modifying the MSL, the result should look like this:
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S"
xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
<EntityContainerMapping StorageEntityContainer="NorthwindModelStoreContainer"
CdmEntityContainer="NorthwindEntities" >
<FunctionImportMapping FunctionImportName="CustWithRecentOrders"
FunctionName="NorthwindModel.Store.CustWithRecentOrders">
<ResultMapping>
<!-- Name modified for uniformity with new ResultMapping -->
<ComplexTypeMapping TypeName="NorthwindModel.CustWithRecentOrders_Result1">
<ScalarProperty Name="CustomerID" ColumnName="CustomerID" />
<ScalarProperty Name="CompanyName" ColumnName="CompanyName" />
<ScalarProperty Name="ContactName" ColumnName="ContactName" />
<ScalarProperty Name="Phone" ColumnName="Phone" />
</ComplexTypeMapping>
</ResultMapping>
<!-- Added ResultMapping -->
<ComplexTypeMapping TypeName="NorthwindModel.CustWithRecentOrders_Result2">
<ScalarProperty Name="OrderID" ColumnName="OrderID" />
<ScalarProperty Name="OrderDate" ColumnName="OrderDate" />
<ScalarProperty Name="RequiredDate" ColumnName="RequiredDate" />
<ScalarProperty Name="ShippedDate" ColumnName="ShippedDate" />
</FunctionImportMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
Our program will execute the stored procedure and then use the initial result set to gain access to subsequent result sets. As you type, you will notice that the classes generated by the model yield full IntelliSense, enabling you to use strong typing with multiple result sets.
The full content of the program is shown here:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MultipleResultSets
{
class Program
static void Main(string[] args)
using (var context = new NorthwindEntities())
var r1 = context.CustWithRecentOrders("ALFKI");
foreach (var result in r1)
Console.WriteLine(
"{0} ({1})",
result.ContactName,
result.CompanyName);
}
var r2 = r1.GetNextResult<MultipleResultSets.CustWithRecentOrders_Result2>();
foreach (var result in r2)
"{0} was ordered on {1}, required on {2} and shipped on {3}",
result.OrderID,
result.OrderDate,
result.RequiredDate,
result.ShippedDate);
Let’s walk through the code above. First we create a context with a using statement. Next we iterate through that first result set and write the contact information to the screen.
When you run the program, you can see that multiple result sets can be accessed from a single stored procedure.
If you place a breakpoint on the first foreach and attach SQL Profiler, you will note that stored procedures are executed immediately, unlike many other EF queries that are deferred until the results are iterated upon.
In this walkthrough we created a stored procedure that returned multiple result sets and successfully used each of those result sets in a console application. We dug into the internals of the CSDL and MSL because the tooling experience for multiple result sets is incomplete in the June 2011 CTP. We do expect that tooling experience to be complete when this version of EF is released. This CTP is an especially important time to provide feedback that may affect the finished product. Please leave your question and comments below.
ADO.NET Entity Framework Team