65.9K
CodeProject 正在变化。 阅读更多。
Home

使用函数导入映射从 Entity Framework 存储过程返回多个结果集

刘神威
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.96/5 (35投票s)

2013年10月31日

CPOL

6分钟阅读

viewsIcon

199683

downloadIcon

2771

如何修复返回多个结果集的 EF 存储过程的函数导入映射

引言

自 Entity Framework (EF) 5.0 起，就支持从存储过程中返回多个结果集。然而，即使从 EF 6.0 开始，使用 EF 设计器对该功能进行映射配置的方法也一直未提供。MSDN 仅发布了关于返回具有简单实体类型的多个结果集的有限说明。任何业务数据应用程序或多或少都需要从存储过程中检索多个数据结果集以避免多次调用。此外，存储过程的返回类型通常比简单实体类型更复杂。本文详细介绍了如何在 EF 设计器中配置存储过程的函数导入映射，以返回具有复杂类型的多个结果集。

2015年7月22日消息更新：为了回应一些读者的问题和疑虑，我添加了另一个 Visual Studio 解决方案示例，说明如何处理从存储过程返回的三个结果集。返回更多结果集也应遵循类似方法。为清晰起见，文章正文未更新，仍描述两个结果集的情况。读者可以在评论和讨论区查看更多详情。

所需工具

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

带有存储过程的示例数据库

您可以在 SQL Server 2012 Express 或 LocalDB 中创建数据库，并通过使用 Visual Studio 或 SQL Server Management Studio (SSMS) 执行包含的脚本文件 StoreDB.sql 来填充表。您可以此处下载 SQL Server Express (含 LocalDB) 和 SSMS。

执行脚本还将向数据库中添加两个存储过程。我将第二个存储过程视为虚拟存储过程，它仅用于 EF 自动提取第二个结果集复杂类型信息，因为 EF 只能从第一个结果集中提取返回字段信息。虚拟存储过程中的查询与第一个存储过程中第二个结果集的查询完全相同。在完成 EF 映射后，可以从数据库中删除虚拟存储过程。

返回两个结果集的第一个存储过程

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

用于 EF 提取复杂类型信息的第二个存储过程

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

创建包含实体数据模型的 Visual Studio 项目

MSDN 教程中描述了创建实体数据库优先应用程序项目的详细信息。按照构建应用程序的步骤进行操作，但使用上面描述的示例数据库，模型名称为 StoreDBModel，数据库连接字符串名称为 StoreDBEntities。

将存储过程添加到实体数据模型

<FunctionImport Name="GetAllCategorisAndProducts" 
 ReturnType="Collection(StoreDBModel.Category_SprocResult)" />
<FunctionImport Name="GetProductsCM" 
 ReturnType="Collection(StoreDBModel.Product_SprocResult)" />

更改第一个 FunctionImport 节点中的代码，如下所示。这实际上是将第二个返回类型合并到第一个 FunctionImport 节点中。第二个 ReturnType 节点中的 Type 属性可以很容易地从第二个 FunctionImport 节点复制。合并后，只需将第二个 FunctionImport 节点留在那里，因为它将在我们稍后从模型浏览器中进行清理时自动删除。

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

将第二个 FunctionImportMapping 中的 ResultMapping 节点添加到第一个 FunctionImportMapping 中。目前先将整个第二个 FunctionImportMapping 节点保留在那里。

<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. 通过在解决方案资源管理器中单击 StoreDBModel.edmx 文件来打开 EF 设计器。然后右键单击设计器上的任何空白区域并选择从数据库更新模型…。这将打开更新向导窗口。
  2. 从“添加”选项卡中的“存储过程和函数”列表中选择这两个存储过程。确保选中“将选定的存储过程和函数导入到实体模型中”，然后单击“完成”按钮。这将自动添加存储过程的函数导入映射和复杂类型。
  3. 右键单击 EF 设计器上的任何空白区域并选择模型浏览器。在模型浏览器中，将复杂类型名称 GetAllCategorisAndProducts_Result 更改为 Category_SprocResult，将 GetProductCM_Result 更改为 Product_SprocResult，如下所示。
  4. 保存 StoreDBModel.edmx 文件。已创建了具有我们更改名称的两个复杂类型对象。现在我们需要手动编辑 StoreDBModel.edmx 文件的 XML 内容。右键单击该文件，选择打开方式…，然后选择XML（文本）编辑器。在 edmx:ConceptualModels 节点下找到 FunctionImport 节点。
  5. 在 edmx:Mappings/../En<FunctionImportMapping 节点下找到 FunctionImportMapping 节点。
  6. 再次打开模型浏览器，清理虚拟存储过程设置。删除存储过程/函数和函数导入列表中的 GetProductCM。这将自动删除 GetProductCM 存储过程映射的所有设置，以及 StoreDBModel.Context.cs 文件中调用虚拟存储过程的方法。
  7. 保存 StoreDBModel.edmx 文件。所有设置更改和清理都将生效。

更新模型时会发生什么

当由于任何数据库模式更改而更新模型时，手动编辑的用于从存储过程返回多个结果集的设置是否会被覆盖？根据我使用 Visual Studio 2013 测试的结果，在向模型中添加实体或其他存储过程，或从模型中删除任何项目时，所有编辑更改都保持不变，但删除或刷新已编辑的存储过程映射除外。

当在存储过程中添加或更改输入/输出参数时，如果从更新数据库模型（更新向导）屏幕执行刷新选项卡，更新将自动在模型中刷新。例如，在数据库中向存储过程 GetAllCategorisAndProducts 添加 @Test nvarchar(50) 作为输入参数，然后刷新模型将把 Parameter 节点插入到存储过程的 FunctionImport 节点中，即使它之前是手动编辑的。

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

在任何版本的 EF 中，都不支持由于返回字段的更改而自动刷新存储过程复杂类型映射，即使对于返回单个结果集的存储过程也是如此。我们需要在从模型中删除存储过程和函数导入映射后，重新将存储过程添加到模型中，或者使用模型浏览器或 XML 编辑器手动更新复杂类型。

执行代码调用存储过程

在函数导入期间，调用存储过程的基本方法会自动添加到数据上下文对象中。在实际应用中，从调用返回的多个数据集应插入到包含具有复杂类型的多个可枚举集合的对象中。然后，该对象将返回给客户端调用者。这些过程通常与 EF 数据模型一起位于单独的程序集中，作为数据访问层 (DAL)。

以下是 PartialDAL.cs 中的方法，用于使用来自两个返回结果集的数据填充具有两个集合的对象。

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;
    }
}

返回对象类型代码如下：

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

现在我们可以调用 GetAllCategriesAndProducts() 方法并在控制台窗口中显示结果。

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);
}

摘要

尽管 EF 数据模型设计器仍然不支持返回多个结果集的存储过程的自动函数导入映射，但此处描述了一种简单实用的解决方法，可以使此类存储过程在基于 EF EDMX 的数据应用程序中正常工作。

历史

  • 2013年10月30日：原始发布
  • 2015年7月22日：添加了更新的示例和源代码，以帮助一些读者解决从存储过程返回超过两个数据结果集的问题
© . All rights reserved.