使用实体框架核心1.1执行存储过程错误“错误”:“给定的密钥不存在于字典中”
问题描述:
我有以下用户定义过程从数据库调用计算数据与2参数返回一个整数(*))使用实体框架核心1.1执行存储过程错误“错误”:“给定的密钥不存在于字典中”
USE [QIIS2]
GO
PROCEDURE [dbo].[sp.GetCansTotals]
@startdt datetime2,
@enddt datetime2,
@hospitalId int
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT(*) AS TotalCancelled
FROM Cans
WHERE (OperDt BETWEEN @startdt AND @enddt)
AND (hospitalId = @hospitalId);
END
存储库调用的过程:
public IEnumerable<Can> getcanstotal(int @hospitalId, string @startdt, string @enddt)
{
_context.Database.OpenConnection();
DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "GetCansTotals";
cmd.CommandType = CommandType.StoredProcedure;
IEnumerable<Can> can;
using (_context)
{
can = _context.LoadStoredProc("GetCansTotals")
.WithSqlParam("@hospitalId", @hospitalId)
.WithSqlParam("@startdt", @startdt)
.WithSqlParam("@enddt", @enddt)
.ExecuteStoredProc<Can>();
}
return can;
}
和控制器:
[HttpGet("byParams")]
public IActionResult GetCanTotals(int hospitalId, string startdt, string enddt)
{
var cans = _unitOfWork.Cans.getcanstotal(hospitalId, startdt, enddt);
return Ok(Mapper.Map<IEnumerable<CanViewModel>>(cans));
}
以下是ExecStoredProced和马平的EFExctensions.cs:
namespace DAL.Extensions
{
public static class EFExtensions
{
/// <summary>
/// Creates an initial DbCommand object based on a stored procedure name
/// </summary>
/// <param name="context"></param>
/// <param name="storedProcName"></param>
/// <returns></returns>
public static DbCommand LoadStoredProc(this DbContext context, string storedProcName)
{
var cmd = context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = storedProcName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
return cmd;
}
/// <summary>
/// Creates a DbParameter object and adds it to a DbCommand
/// </summary>
/// <param name="cmd"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DbCommand WithSqlParam(this DbCommand cmd, string paramName, object paramValue)
{
if (string.IsNullOrEmpty(cmd.CommandText) && cmd.CommandType != System.Data.CommandType.StoredProcedure)
throw new InvalidOperationException("Call LoadStoredProc before using this method");
var param = cmd.CreateParameter();
param.ParameterName = paramName;
param.Value = paramValue;
cmd.Parameters.Add(param);
return cmd;
}
/// <summary>
/// Executes a DbDataReader and returns a list of mapped column values to the properties of <typeparamref name="T"/>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="command"></param>
/// <returns></returns>
public static IList<T> ExecuteStoredProc<T>(this DbCommand command)
{
using (command)
{
if (command.Connection.State == System.Data.ConnectionState.Closed)
command.Connection.Open();
try
{
using (var reader = command.ExecuteReader())
{
return reader.MapToList<T>();
}
}
finally
{
command.Connection.Close();
}
}
}
/// <summary>
/// Retrieves the column values from the stored procedure and maps them to <typeparamref name="T"/>'s properties
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <returns>IList<<typeparamref name="T"/>></returns>
private static IList<T> MapToList<T>(this DbDataReader dr)
{
var objList = new List<T>();
var props = typeof(T).GetRuntimeProperties();
var colMapping = dr.GetColumnSchema()
.Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
.ToDictionary(key => key.ColumnName.ToLower());
if (dr.HasRows)
{
while (dr.Read())
{
T obj = Activator.CreateInstance<T>();
foreach (var prop in props)
{
var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
prop.SetValue(obj, val == DBNull.Value ? null : val);
}
objList.Add(obj);
}
}
return objList;
}
}
}
当通过与邮递员请求:
http://localhost:56963/api/cansdatas/byParams?hospitalId=2&startdt=2016-01-01&enddt=2016-12-31
错误:
"error": "The given key was not present in the dictionary."
莫非任一项帮助??
解决只是删除了执行存储过程和使用下面的代码:
public async Task<IEnumerable<Can>> Get()
{
return await appContext.Cans.FromSql("GetCansList").ToArrayAsync();
}
答
解决的只是删除了执行存储过程和使用下面的代码:
public async Task<IEnumerable<Can>> Get()
{
return await appContext.Cans.FromSql("GetCansList").ToArrayAsync();
}
你向我们展示一个过程'GetCanTotals'需要三个参数 - 但是你所调用的方法是调用'GetCansList'(看起来好像是其他的*存储过程),它似乎需要**没有参数** - 所以你不能提供参数给存储过程不期望任何....或者你只是简单地打电话给你**错误的**存储过程在你的C#代码中? –
你是对的,我修复了程序名现在我得到以下错误“错误”:“找不到存储过程sp.GetCansTotals”。 –
您的存储过程被定义为dbo.sp.GetCansTotals。对我来说,读取为数据库= dbo,schema = sp,存储过程= GetCansTotals。你可以把它定义为dbo.GetCansTotals并更新你的引用来匹配,看看它是否有效? – natwallbank