代码改变世界

【转】Entity Framework 学习中级篇2—存储过程

  AnyKoro  阅读(927)  评论(0)  编辑  收藏  举报

目前，EF对存储过程的支持并不完善。存在以下问题：

　　l　　　　　　　　 EF不支持存储过程返回多表联合查询的结果集。

　　l　　　　　　　　 EF仅支持返回返回某个表的全部字段，以便转换成对应的实体。无法支持返回部分字段的情况。

　　l　　　　　　　　 虽然可以正常导入返回标量值的存储过程，但是却没有为我们自动生成相应的实体.cs代码，我们还是无法在代码中直接调用或使用标量存储过程

　　l　　　　　　　　 EF不能直接支持存储过程中Output类型的参数。

　　l　　　　　　　　 其他一些问题。

　　下面，主要针对如何使用存储过程，以及存储返回实体、表的部分字段这个几个问题，做具体介绍。

　　l　　　　　　　　 导入存储过程及返回实体

　　在VS可视化设计器中，打开实体模型（emdx文件）。然后，鼠标右键点击“Customers”à “添加”à“函数导入”，然后选择“存储过程名称”，并输入函数导入名称，选择返回类型为实体并选择Customers。如下图所示：

Entity Framework 学习中级篇2—存储过程(上)

　　图片看不清楚？请点击这里查看原图（大图）。

　　之后，点击“确定”。之后，存储过程导入。在代码我们就可以使用改存储过程了。如下代码所示：

[Test]
　　　　　　　 public void GetEntityBySP()
　　　　　　　 {
　　　　　　　　　　　 using (var db = new NorthwindEntities())
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 var cst = db.GetCustomerById("ALFKI").FirstOrDefault();
　　　　　　　　　　　　　　　 Assert.IsNotNull(cst);
　　　　　　　　　　　　　　　 Console.WriteLine("CustomerId:{0},ContactName:{1}", cst.CustomerID, cst.ContactName);
　　　　　　　　　　　 }
　　　　　　　 }

l　　　　　　　　 联合查询结果集的问题

　　在此版本的EF中，是不支持存储过程的多张表联合查询的，也就是说查询的结果集中，一部分字段来自表A，另一部分字段来自表B，像这种情况，目前EF无法直接进行处理。为此，可以通过写两个或多个存储过程来实现。比如：第一个存储过程返回表A的所有字段，第二存储过程返回表B的所有字段；然后，我们在代码中来实现联合的查询。

　　按照前面的思路，增加一个返回所有的Orders表字段的存储过程GetOrdersByCustomerId，再增加一个返回Order Details表全部字段的存储过程GetDetailsByCustomerId，并将它们导入到实体模型中。

　　其中，GetOrdersByCustomerId存储过程如下：

CREATE PROCEDURE GetOrdersByCustomerId
　　　　　　　　 @CustomerId varchar(5)
AS
BEGIN
　　　　　　　　 SET NOCOUNT ON;
　　　　　　　　 SELECT * FROM orders WHERE orders.CustomerID=@CustomerId;
END

　　GetDetailsByCustomerId存储过程如下：

CREATE PROCEDURE GetDetailsByCustomerId
@CustomerId varchar(5)　　　　　　　
AS
BEGIN
　　　　　　　　 SET NOCOUNT ON;
　　　　　　　　 SELECT d.*
　　　 　　FROM 　Orders o,[Order Details] d
　　　　　　　　 WHERE o.OrderId=d.OrderId 　AND o.CustomerId=@CustomerId;
END

　　之后，在我们的代码来实现联合查询。代码如下：

[Test]
　　　　　　　 public void GetOrderBySp()
　　　　　　　 {
　　　　　　　　　　　 using (var db = new NorthwindEntities())
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 var orders = db.GetOrdersByCustomerId("VINET").ToList();
　　　　　　　　　　　　　　　 var details = db.GetDetailsByCustomerId("VINET").ToList();
　　　　　　　　　　　　　　 　orders.ForEach(o => o.Order_Details.Attach(details.Where(d => d.OrderID == o.OrderID)));
　　　　　　　　　　　　　　　 foreach (var order in orders)
　　　　　　　　　　　　　　　 {
　　　　　　　　　　　　　　　　　　　 Console.WriteLine(order.OrderID);
　　　　　　　　　　　　　　　　　　　 foreach (var d in order.Order_Details)
　　　　　　　　　　　　　　　　　　　　　　　 Console.WriteLine(d.ProductID);
　　　　　　　　　　　　　　　 }
　　　　　　　　　　　 }
　　　　　　　 }

其中，需要注意的，由于是分别执行了两个存储，在内存中是以两个对立的对象存在，它们之前是没有建立联系的。为此，我们需要使用Attach方法来把他们联系起来(红色代码段)，这样我们就可以通过导航来访问对象的实体了，也就是foreach (var d in order.Order_Details)中的order.Order_Details。

　　l　　　　　　　　 返回部分字段的问题

　　默认情况，目前此版本的EF在使用存储过程返回实体的时候，必须返回所有的字段，以便是EF能够自动将返回的结果转换成对应的实体。否则会报“数据读取器与指定的XXX类型不兼容的异常，….”。

　　接下来，我们通过建立一个存储过程，并建立新建立一个实体来解决此问题。首先，我们在数据库中建立一个名为GetCustomerAndOrders的存储过程，其定义如下：

CREATE PROCEDURE GetCustomerAndOrders
　　　 AS
BEGIN
　　　 SET NOCOUNT ON;
　　　 SELECT c.CustomerID,c.CompanyName,o.OrderID,o.OrderDate,d.Quantity
　　　 FROM Customers c,Orders o,[Order Details] d
　　　 WHERE c.CustomerID=o.CustomerID AND o.OrderID=d.OrderID;
END
GO

　　然后，添加一个实体CustomerOders，并设置属性如下图所示：

Entity Framework 学习中级篇2—存储过程(上)

　　图片看不清楚？请点击这里查看原图（大图）。

　　然后，在VS可视化设计器中，打开实体模型（emdx文件），通过添加à函数导入，导入存储过程GetCustomerAndOrders，并取名为GetCustomerAndOrders，返回类型设置为实体CustomerOrders。

最后，我们就可以代码实体此实体和存储过程了。如下代码：

[Test]
　　　　　　　 public void GetOrderCountByCustomerId()
　　　　　　　 {
　　　　　　　　　　 　using (var db = new NorthwindEntities())
　　　　　　　　　　　 {
　　　　　　　　　 　　　　　　var co = db.GetCustomerAndOrders().Take(10).Skip(0);
　　　　　　　　　　　　　　　 foreach(var c in co)
　　　　　　　　　　　　　　 　　　　　　　Console.WriteLine(c.CustomerID);
　　　　　　　　　　　 }
　　　　　　　 }

　　l　　　　　　　　 返回标量值问题

　　目前，EF对存储过程返回标量值的支持并没有完全。虽然，我们可以按照前面的步骤导入函数，并设置返回标量值的类型，同时EF会在实体模型文件为我们自动生成此存储过程相关的映射配置等。但是，EF却没有为我们生成在实体模型cs文件中，自动生成相应的.cs代码，所以我们根本无法直接调用此存储过程。为解决此问题，我们需要手动添加代码到实体模型的.cs代码文件中。

　　首先，在数据库中建立存储存储过程GetOrderCntByCustomerId，代码如下：

CREATE PROCEDURE GetOrderCntByCustomerId
　　　　　　　　 @CustomerId varchar(5)
AS
BEGIN
　　　　　　　　 SET NOCOUNT ON;
　　　　　　　　 SELECT count(*) FROM Orders WHERE Orders.CustomerId=@CustomerId;
END

　　接着，按照正常的步骤，导入此存储过程并设置返回类型为标量类型的Int32。

　　然后，我们需要添加一个泛型的方法和一个执行存储过程的方法，代码如下：

 

public partial class NorthwindEntities
{
　　 //泛型方法用于执行标量存储过程
　　　　　　　 private T ExecuteFunction<T>(string functionName, System.Data.EntityClient.EntityParameter[] parameters) where T : struct
　　　　　　　 {
　　　　　　　　　　　 System.Data.EntityClient.EntityCommand cmd = ((System.Data.EntityClient.EntityConnection)this.Connection).CreateCommand();
　　　　　　　　　　　 cmd.CommandType = System.Data.CommandType.StoredProcedure;
　　　　　　　　　　　 cmd.Parameters.AddRange(parameters);
　　　　　　　　　　　 cmd.CommandText = this.DefaultContainerName + "." + functionName;
　　　　　　　　　　　 try
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 if (cmd.Connection.State != System.Data.ConnectionState.Open)
　　　　　　　　　　　　　　　　　　　 cmd.Connection.Open();
　　　　　　　　　　　　　　　 var obj = cmd.ExecuteScalar();
　　　　　　　　　　　　　　　 return (T)obj;
　　　　　　　　　　　 }
　　　　　　　　　　　 catch (System.Exception)
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 throw;
　　　　　　　　　　　 }
　　　　　　　　　　　 finally
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 cmd.Connection.Close();
　　　　　　　　　　　 }
　　　　　　　 }
　　　　　　　 //执行数据库中GetOrderCntByCustomerId存储过程的方法
　　　　　　　 public int GetOrderCountByCustomerId(string CustomerId)
　　　　　　　 {
　　　　　　　　　　　 var param = new System.Data.EntityClient.EntityParameter("CustomerId", System.Data.DbType.String);
　　　　　　　　　　　 param.Value = CustomerId;
　　　　　　　　　　　 return ExecuteFunction<int>("GetOrderCountByCustomerId", new[] { param });
　　　　　　　 }
}

　　最后，通过以上修改，我们就可以直接使用返回标量值的存储过程，代码如下：

[Test]
　　　　　　　 public void GetOrderCountByCustomerId()
　　　　　　　 {
　　　　　　　　　　　 using (var db = new NorthwindEntities())
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 var result = db.GetOrderCountByCustomerId("VINET");
　　　　　　　　　　　　　　　 Assert.Greater(result, 0);
　　　　　　　　　　　　　　　 Console.WriteLine(result.ToString());
　　　　　　　　　　　 }
　　　　　　　 }

　　 至此，我们就解决了EF存储过程返回标量的问题。

目前，EF对存储过程的支持并不完善。存在以下问题：

　　l　　　　　　　　 EF不支持存储过程返回多表联合查询的结果集。

　　l　　　　　　　　 EF仅支持返回返回某个表的全部字段，以便转换成对应的实体。无法支持返回部分字段的情况。

　　l　　　　　　　　 虽然可以正常导入返回标量值的存储过程，但是却没有为我们自动生成相应的实体.cs代码，我们还是无法在代码中直接调用或使用标量存储过程

　　l　　　　　　　　 EF不能直接支持存储过程中Output类型的参数。

　　l　　　　　　　　 其他一些问题。

　　本节，我们将学习如何手动添加/修改存储过程，如何使EF能够支持Output类型的参数。

　　l　　　　　　　　 添加/修改存储过程

　　有时候，某个SQL语句比较复杂，但是数据库中又没有定义相应的存储过程。这个时候，我们又想使上层代码比较简单、方便的方式来完成此项任务。那么，此时，我们便可以手工在实体模型（.edmx文件）中添加自己需要的存储过程了。这样既方便上层调用又方便后期的修改。

　　以手动修改实体模型edmx文件，添加名为CustomerByCommandText的存储过程为例。具体步骤如下：

　　修改实体模型文件，找到ssdl部分，添加如下代码：

　　　　　　　 <Function Name="CustomerByCommandText" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" >
　　　　　　　　　 <CommandText>
　　　　　　　　　　　 select c.* from Customers c,Orders o where c.CustomerID=o.CustomerID
　　　　　　　　　 </CommandText>
　　　　　　　 </Function>

<FunctionImport Name="CustomerByCommandText" EntitySet="Customers" ReturnType="Collection(NorthwindModel.Customers)"></FunctionImport>

　　接着，找到msl部分，添加如下代码：

<FunctionImportMapping FunctionImportName="CustomerByCommandText" FunctionName="NorthwindModel.Store.CustomerByCommandText"/>

　　最后，在实体模型的.cs文件里面，添加一个执行此存储过程的方法，代码如下：

　　　　　　　 public global::System.Data.Objects.ObjectResult<Customers> GetCustomerByCommandText()
　　　　　　　 {
　　　　　　　　　　　 return base.ExecuteFunction<Customers>("CustomerByCommandText");
　　　　　　　 }

　　至此，修改完毕。

　　现在，我们就可以在代码使用刚才手工定义的存储过程了。如下代码所示：

[Test]
　　　　　　　 public void GetCustomerByCmdText()
　　　　　　　 {
　　　　　　　　　　　 using (var db = new NorthwindEntities())
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 var csts = db.GetCustomerByCommandText().Take(10).Skip(0);
　　　　　　　　　　　　　　　 foreach (var c in csts)
　　　　　　　　　　　　　　　　　　　 Console.WriteLine(c.CustomerID);
　　　　　　　　　　　 }
　　　　　　　 }

　　其实，关键的地方就是CommandText这个部分的内容，它里面就是要执行的SQL语句。另外，我们可以在修改实体模型emdx文件的同时，我们可以看到所有的实体类查询的SQL语句命令都可以在edmx文件里找到，我们都可以进行相应的修改。

l　　　　　　　　 Output类型参数

　　在实际应用当中，很多时候，我们需要使用output类型的存储过程参数，以便返回我们需要的值。但是，目前，EF不能直接支持output类型的存储过程参数。为此，我们需要对实体模型进行修改，以便使其支持output类型的输出参数。具体过程如下：

　　在数据库中建立一个为名的GetNameByCustomerId存储过程，代码如下：

CREATE PROCEDURE GetNameByCustomerId
　@CustomerId varchar(5),
　@ContactName varchar(30) output
AS
BEGIN
　SET NOCOUNT ON;
　SELECT @ContactName=ContactName
　FROM Customers
　WHERE CustomerID=@CustomerId;
END

　　然后，开始修改实体模型edmx文件。

　　先找到ssdl定义的部分，添加如下代码：

　　　　　 <Function Name="GetNameByCustomerId" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
　　　　　　　　　 <Parameter Name="CustomerId" Type="varchar" Mode="In" MaxLength="5"></Parameter>
　　　　　　　　　 <Parameter Name="ContactName" Type="varchar" Mode="Out" MaxLength="30"></Parameter>
　　　　　　 </Function>

　　接着，在找到csdl定义的部分，添加如下代码：

　　　　　 <FunctionImport Name="GetNameByCustomerId">
　　　　　　　　　　　 <Parameter Name="CustomerId" Mode="In" Type="String" MaxLength="5"></Parameter>
　　　　　　　　　　　 <Parameter Name="ContactName" Mode="Out" Type="String" MaxLength="30"></Parameter>
　　　　　 </FunctionImport>

最后，找到msl定义的部分，添加如下代码：

<FunctionImportMapping FunctionImportName="GetNameByCustomerId" FunctionName="NorthwindModel.Store.GetNameByCustomerId"></FunctionImportMapping>

　　至此，实体模型emdx文件修改完毕。

　　接下来，我们需要在实体模型的.cs文件中，增加相应的调用方法。代码如下：

　　　 public partial class NorthwindEntities1
{
　
//执行GetNameByCustomerId的方法
　　　　　　　 public void GetNameByCustomerId(string CustomerId, out string ContactName)
　　　　　　　 {
　　　　　　　　　　 ContactName = string.Empty;
　　　　　　　　　　　 var Pars = new System.Data.EntityClient.EntityParameter[]
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 new System.Data.EntityClient.EntityParameter{ ParameterName="CustomerId", DbType=System.Data.DbType.String,Value=CustomerId},
　　　　　　　　　　　　　　　 new System.Data.EntityClient.EntityParameter{ParameterName="ContactName", DbType=System.Data.DbType.String, Direction=System.Data.ParameterDirection.Output}
　　　　　　　　　　　 };
　　　　　　　　　　　 this.ExecuteNonQuery("GetNameByCustomerId", Pars);
　　　　　　　　　　　 ContactName = Pars[1].Value.ToString();
　
　　　　　　　 }
　　　　　
　　　　　 //辅助方法，执行SQL命令
　　　　　　　 private void ExecuteNonQuery(string functionName, System.Data.EntityClient.EntityParameter[] parameters)
　　　　　　　 {
　　　　　　　　　　　 System.Data.EntityClient.EntityCommand cmd = ((System.Data.EntityClient.EntityConnection)this.Connection).CreateCommand();
　　　　　　　　　　　 cmd.CommandType = System.Data.CommandType.StoredProcedure;
　　　　　　　　　　　 cmd.Parameters.AddRange(parameters);
　　　　　　　　　　　 cmd.CommandText = this.DefaultContainerName + "." + functionName;
　　　　　　　　　　　 try
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 if (cmd.Connection.State != System.Data.ConnectionState.Open)
　　　　　　　　　　　　　　　　　　　 cmd.Connection.Open();
　　　　　　　　　　　　　　　 cmd.ExecuteNonQuery();
　　　　　　　　　　　 }
　　　　　　　　　　　 catch (System.Exception)
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 throw;
　　　　　　　　　　　 }
　　　　　　　　　　　 finally
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 cmd.Connection.Close();
　　　　　　　　　　　 }
　　　　　　　 }
}

　　现在，所有的修改工作都做完了。接下来，我们就可以在代码中直接调用此存储过程了。示例代码如下：

[Test]
　　　　　　　 public void OutputTest()
　　　　　　　 {
　　　　　　　　　　　 using (var db = new NorthwindModel.NorthwindEntities1())
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 string contactname = string.Empty;
　　　　　　　　　　　　　　　 db.GetNameByCustomerId("ALFKI", out contactname);
　　　　　　　　　　　　　　　 Assert.IsTrue(!string.IsNullOrEmpty(contactname));
　　　　　　　　　　　　　　　 Console.WriteLine(contactname);
　　　　　　　　　　　 }
　　　　　　　 }

　　至此，我们便可以使用Output类型的输出参数了。

编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
历史上的今天：
2009-03-10 DinnerNow.Net中的AJAX应用体味（3）
2009-03-10 DinnerNow.Net中的AJAX应用体味（2）
点击右上角即可分享
微信分享提示
AI IDE Trae