EF4.1代码优先：带有输出参数的存储过程 [英] EF4.1 Code First: Stored Procedure with output parameter

问题描述

我使用实体框架4.1代码。我想调用一个具有输出参数的存储过程，并检索该输出参数的值，以及强类型结果集。它具有这样的签名的搜索功能

  public IEnumerable< MyType>搜索（int maxRows，out int totalRows，string searchTerm）{...} 
  

我发现很多提示功能导入，但与Code First不兼容。
我可以使用Database.SqlQuery（...）调用存储过程，但是不能使用输出参数。

我可以使用EF4解决这个问题。 1代码首先？

解决方案

SqlQuery 使用输出参数，您必须正确定义SQL查询和设置 SqlParameter s。尝试一下：

  var outParam = new SqlParameter（）; 
 outParam.ParameterName =TotalRows; 
 outParam.SqlDbType = SqlDbType.Int; 
 outParam.ParameterDirection = ParameterDirection.Output; 
 
 var data = dbContext.Database.SqlQuery< MyType>（sp_search @SearchTerm，@MaxRows，@TotalRows OUT，
 new SqlParameter（SearchTerm，searchTerm），
新的SqlParameter（MaxRows，maxRows），
 outParam）; 
 var result = data.ToList（）; 
 totalRows =（int）outParam.Value; 
  

I use Entity Framework 4.1 Code First. I want to call a stored procedure that has an output parameter and retrieve the value of that output parameter in addition to the strongly typed result set. Its a search function with a signature like this

public IEnumerable<MyType> Search(int maxRows, out int totalRows, string searchTerm) { ... }

I found lots of hints to "Function Imports" but that is not compatible with Code First. I can call stored procedures using Database.SqlQuery(...) but that does not work with output parameters.

Can I solve that problem using EF4.1 Code First at all?

解决方案

SqlQuery works with output parameters but you must correctly define SQL query and setup SqlParameters. Try something like:

var outParam = new SqlParameter();
outParam.ParameterName = "TotalRows";
outParam.SqlDbType = SqlDbType.Int;
outParam.ParameterDirection = ParameterDirection.Output;

var data = dbContext.Database.SqlQuery<MyType>("sp_search @SearchTerm, @MaxRows, @TotalRows OUT", 
               new SqlParameter("SearchTerm", searchTerm), 
               new SqlParameter("MaxRows", maxRows),
               outParam);
var result = data.ToList();
totalRows = (int)outParam.Value;

这篇关于EF4.1代码优先：带有输出参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！