Click here to Skip to main content
Click here to Skip to main content

Returning Multiple Result Sets from an Entity Framework Stored Procedure with Function Import Mappings

, 31 Oct 2013
Rate this:
Please Sign up or sign in to vote.
A practical workaround to fix the Function Import mappings for an EF stored procedure returning multiple result sets.

Introduction

Returning multiple result sets from a stored procedure has been supported since Entity Framework (EF) 5.0. However, the approach of mapping configurations for the feature using the EF designer has not been available even from EF 6.0. MSDN only posted limited instructions on the topic of returning multiple result sets with simple entity types. Any business data application more or less needs to retrieve multiple result sets of data from stored procedures to avoid multiple calls. In addition, returning types of stored procedures are usually more complex than the simple entity types. This article provides the details of configuring the Function Import mappings for a stored procedure in the EF designer to return multiple result sets with complex types.

Required Tools

  • Visual Studio 2012 or 2013
  • .NET Framework 4.5
  • Entity Framework 5.0 or 6.0

Sample Database with Stored Procedures

You can create the database in the SQL Server 2012 Express or LocalDB and populate the tables by executing the included script file, StoreDB.sql, using the Visual Studio or the SQL Server Management Studio (SSMS). You can download the SQL Server Express with LocalDB and the SSMS here.

Executing the script will also add two stored procedures into the database. The second stored procedure, I treat it as a dummy, is simply used for EF to automatically extract the second result set complex type info since the EF can only extract the returning field info from the first result set. The query in the dummy stored procedure is exactly the same as that for second result set in the first stored procedure. The dummy stored procedure can be removed from the database after completing the EF mappings.

The first stored procedure that returns two result sets:

CREATE PROCEDURE dbo.GetAllCategorisAndProducts
    SELECT c.CategoryID, 
           c.CategoryName, 
           p.ProductCount
    FROM dbo.Category c 
      JOIN (SELECT count(ProductID) AS ProductCount, CategoryID
            FROM Product    
            GROUP BY CategoryID) p
      ON p.CategoryID = c.CategoryID   
 
SELECT p.ProductID,
        p.ProductName,
        p.CategoryID,
        p.StatusCode,
        s.Description AS StatusDescription,
        p.UnitPrice,
        p.AuditTime
FROM dbo.Product p
JOIN dbo.ProductStatusType s ON s.StatusCode = p.StatusCode   

The second stored procedure used for EF to extract complex type info:

CREATE PROCEDURE dbo.GetProductCM
SELECT p.ProductID,
        p.ProductName,
        p.CategoryID,
        p.StatusCode,
        s.Description AS StatusDescription,
        p.UnitPrice,
        p.AuditTime
FROM dbo.Product p
JOIN dbo.ProductStatusType s ON s.StatusCode = p.StatusCode

Create Visual Studio Project with Entity Data Model

Details to create an entity database-first app project are described in the MSDN tutorial. Follow the steps for building the app except using the sample database described above, the StoreDBModel for the model name, and the StoreDBEntities for the database connection string name.

Add Stored Procedures into the Entity Data Model

  1. Open the EF designer by clicking the StoreDBModel.edmx file on the Solution Explorer. Then right-click any blank area on the designer and select the Update Model from Database…. This will open the Update Wizard window.
  2. Select the two stored procedures from the Stored Procedures and Functions list in the Add tab. Make sure that the Import selected stored procedures and functions into the entity model is checked and then click the Finish button. This will automatically add the function import mappings and the complex types for the stored procedures.
  3. Right-click any blank area on the EF designer and select the Model Browser. In the Model Browser, change the Complex Type name GetAllCategorisAndProducts_Result to Category_SprocResult, and the GetProductCM_Result to Product_SprocResult as shown below.
  4. Save the StoreDBModel.edmx file. The two complex type objects are created with the names we changed. Now we need to manually edit the XML content of the StoreDBModel.edmx file. Right-clicking the file, select the Open With…, and then XML (Text) Editor. Find the FunctionImport nodes under the edmx:ConceptualModels node:
  5. <FunctionImport Name="GetAllCategorisAndProducts" ReturnType="Collection(StoreDBModel.Category_SprocResult)" />
    <FunctionImport Name="GetProductsCM" ReturnType="Collection(StoreDBModel.Product_SprocResult)" />

    Change the code in the first FunctionImport node as shown below. This is actually to merge the second return type into the first FunctionImport node. The Type attribute in the second ReturnType node can easily be copied from the second FunctionImport node. After the merging, just leave the second FunctionImport node there as it will automatically be deleted when we do the clean-up from the Model Browser later.

    <FunctionImport Name="GetAllCategorisAndProducts">
      <ReturnType Type="Collection(StoreDBModel.Category_SprocResult)" />
      <ReturnType Type="Collection(StoreDBModel.Product_SprocResult)" />
    </FunctionImport>          
    <FunctionImport Name="GetProductCM" 
         ReturnType="Collection(StoreDBModel.Product_SprocResult)" />
  6. Find the FunctionImportMapping nodes under the edmx:Mappings/../En<FunctionImportMapping node.
  7. <FunctionImportMapping FunctionImportName="GetAllCategorisAndProducts" 
             FunctionName="StoreDBModel.Store.GetAllCategorisAndProducts">
      <ResultMapping>
         <ComplexTypeMapping TypeName="StoreDBModel.Category_SprocResult">
            <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
            <ScalarProperty Name="CategoryName" ColumnName="CategoryName" />
            <ScalarProperty Name="ProductCount" ColumnName="ProductCount" />
         </ComplexTypeMapping>
      </ResultMapping>
    </FunctionImportMapping>
    <FunctionImportMapping FunctionImportName="GetProductCM" FunctionName="StoreDBModel.Store.GetProductCM">
      <ResultMapping>
         <ComplexTypeMapping TypeName="StoreDBModel.Product_SprocResult">
            <ScalarProperty Name="ProductID" ColumnName="ProductID" />
            <ScalarProperty Name="ProductName" ColumnName="ProductName" />
            <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
            <ScalarProperty Name="StatusCode" ColumnName="StatusCode" />
            <ScalarProperty Name="StatusDescription" ColumnName="StatusDescription" />
            <ScalarProperty Name="UnitPrice" ColumnName="UnitPrice" />
            <ScalarProperty Name="AuditTime" ColumnName="AuditTime" />
         </ComplexTypeMapping>
       </ResultMapping>
    </FunctionImportMapping>

    Add the ResultMapping node from the second FunctionImportMapping into the first FunctionImportMapping. Leave the entire second FunctionImportMapping node there for now.

    <FunctionImportMapping FunctionImportName="GetAllCategorisAndProducts" 
               FunctionName="StoreDBModel.Store.GetAllCategorisAndProducts">
      <ResultMapping>
         <ComplexTypeMapping TypeName="StoreDBModel.Category_SprocResult">
            <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
            <ScalarProperty Name="CategoryName" ColumnName="CategoryName" />
            <ScalarProperty Name="ProductCount" ColumnName="ProductCount" />
         </ComplexTypeMapping>
      </ResultMapping>
      <ResultMapping>  
         <ComplexTypeMapping TypeName="StoreDBModel.Product_SprocResult">
            <ScalarProperty Name="ProductID" ColumnName="ProductID" />
            <ScalarProperty Name="ProductName" ColumnName="ProductName" />
            <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
            <ScalarProperty Name="StatusCode" ColumnName="StatusCode" />
            <ScalarProperty Name="StatusDescription" ColumnName="StatusDescription" />
            <ScalarProperty Name="UnitPrice" ColumnName="UnitPrice" />
            <ScalarProperty Name="AuditTime" ColumnName="AuditTime" />
         </ComplexTypeMapping>
       </ResultMapping>
    </FunctionImportMapping>
    <FunctionImportMapping...>
        . . .
    </FunctionImportMapping> 
  8. Clean up dummy stored procedure settings by opening the Model Browser again. Delete the GetProductCM in both Stored Procedures/Functions and Function Import lists. This will automatically delete all settings for the GetProductCM stored procedure mappings and also the method to call the dummy stored procedure in the StoreDBModel.Context.cs file.
  9. Save the StoreDBModel.edmx file. All changes in settings and clean-up will then be in effect.

What Happen When Updating Model

Will the manual editing for returning multiple result sets from the stored procedure be overwritten when updating the model due to any database schema changes? Based on results of my tests using the Visual Studio 2013, all the editing changes were kept intact when adding entities or other stored procedures into, or deleting any items from, the model except for deleting or refreshing the edited stored procedure mappings.

When adding or changing the input/output parameters in the stored procedure, the updates will automatically be refreshed in the model if executing the Refresh tab from the Update Model from Database (Update Wizard) screen. For example, adding @Test nvarchar(50) as an input parameter to the stored procedure, GetAllCategorisAndProducts, in the database then refreshing the model will insert the Parameter node into the stored procedure’s FunctionImport node even though it was manually edited before.

<FunctionImport Name="GetAllCategorisAndProducts">
  <ReturnType Type="Collection(StoreDBModel.Category_SprocResult)" />
  <ReturnType Type="Collection(StoreDBModel.Product_SprocResult)" />
  <Parameter Name="Test" Mode="In" Type="String" />
</FunctionImport> 

Automatic refreshing stored procedure complex type mappings due to changes in returning fields is not supported in any version of the EF, even for a stored procedure returning a single result set. We need to either re-add the stored procedure to the model after dropping the stored procedure and function import mappings from the model, or manually update the complex type using the Model Browser or the XML editor.

Execute Code to Call Stored Procedure

The base method to call the stored procedure is automatically added into the data context object during the function import. In the real world, the multiple data sets returned from the call should be inserted into the object containing multiple enumerable collections with the complex types. The object, in turn, will be passed back to the client caller. These processes are usually in a separate assembly together with the EF data models as the data access layer (DAL).

Here is the method in the PartialDAL.cs to populate the object with two collections having data from the two returning result sets.

private static CategoriesProducts GetAllCategriesAndProducts()
{
    CategoriesProducts categProd = new CategoriesProducts();
    categProd.Categories = new List<Category_SprocResult>();
    categProd.Products = new List<Product_SprocResult>();
    using (var dbContext = new StoreDBEntities())
    {
        var results = dbContext.GetAllCategorisAndProducts();
        //Get first enumerate result set
        categProd.Categories.AddRange(results);
        //Get second result set
        var products = results.GetNextResult<Product_SprocResult>();
        categProd.Products.AddRange(products);
        //Return all result sets
        return categProd;
    }
} 

The returning object type code is like this.

public class CategoriesProducts 
{
    public List<Category_SprocResult> Categories { get; set; }
    public List<Product_SprocResult> Products { get; set; }        
} 

Now we can call the GetAllCategriesAndProducts() method and display the results in a console window.

CategoriesProducts results = PartialDAL.GetAllCategriesAndProducts();

Console.WriteLine("All categories in the database:");
foreach (var item in results.Categories)
{
     Console.WriteLine(item.CategoryName + "  Product Count: " + item.ProductCount.ToString());
}

Console.WriteLine("All product names in the database:");
foreach (var item in results.Products)
{
     Console.WriteLine(item.ProductName);
}

Summary

Although automatic function import mappings have still not been supported in the EF data model designer for stored procedures returning multiple result sets, there is an easy and practical workaround as described here to make such stored procedures work well in the EF EDMX based data applications.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Shenwei Liu

United States United States
Shenwei is a software developer and architect, and has been working on business applications using Microsoft and Oracle technologies since 1996. He obtained Microsoft Certified Systems Engineer (MCSE) in 1998 and Microsoft Certified Solution Developer (MCSD) in 1999. He has experience in ASP.NET, C#, Visual Basic, Windows and Web Services, Silverlight, WPF, JavaScript/AJAX, HTML, SQL Server, and Oracle.

Comments and Discussions

 
Question"The result of a query cannot be enumerated more than once" error Pinmembersbattagl8-May-14 5:22 
AnswerRe: "The result of a query cannot be enumerated more than once" error PinpremiumShenwei Liu9-May-14 6:19 
QuestionWhether we should use same steps for framework 6.0? Or is there other option Pinmembergeeta.pavate26-Dec-13 2:18 
AnswerRe: Whether we should use same steps for framework 6.0? Or is there other option [modified] PinmemberShenwei Liu26-Dec-13 4:57 
GeneralRe: Whether we should use same steps for framework 6.0? Or is there other option Pinmembergeeta.pavate26-Dec-13 22:43 
QuestionSP with temp table PinmemberMember 789779514-Nov-13 11:03 
AnswerRe: SP with temp table [modified] PinmemberShenwei Liu15-Nov-13 6:21 
GeneralRe: SP with temp table PinmemberMember 789779515-Nov-13 8:49 
GeneralMy vote of 5 PinprofessionalPrasad Khandekar31-Oct-13 21:53 
GeneralRe: My vote of 5 PinmemberShenwei Liu1-Nov-13 14:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 1 Nov 2013
Article Copyright 2013 by Shenwei Liu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid