EF6调用存储过程，返回多个结果集处理

链接：http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram

案例：下载

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

<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 FunctionImportnode 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)" />

<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 firstFunctionImportMapping. 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> 

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 theedmx:ConceptualModels node:
5. Find the FunctionImportMapping nodes under theedmx:Mappings/../En<FunctionImportMapping node.
6. Clean up dummy stored procedure settings by opening the Model Browser again. Delete theGetProductCM 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.
7. 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.