如何将参数传递给返回多条记录的存储过程

Question

如何将参数传递给使用 EF6 返回多条记录的存储过程 -DbContext具有加密列的 SQL Server 2016 存储过程？

使用 EF 6.1.3，SQL Server 2016 - 始终加密(确定性(

有几篇文章讨论了使用 ADO .NET 4.6 通过使用命令、参数对象来实现此目的，但我无法从我的 EFDbContext中找到执行此操作的方法。

Answer 1

//make sure to provide the actual datatype and size on the parameter   
//you can use reflection on the entity to dynamically get/set the property info by a common function.
var paratmeterNames = new StringBuilder();
string paratmeterNamesUpdated = null;
if (lastName != null)
{
var lastNameParam = new SqlParameter("@lastName", SqlDbType.VarChar, 60) {Value = lastName};
parameterList.Add(lastNameParam);
paratmeterNames.Append("@lastName,");
}
if (firstName != null)
{
var firstNameParameter = new SqlParameter("@firstName", SqlDbType.VarChar, 30) {Value = firstName};
parameterList.Add(firstNameParameter);
paratmeterNames.Append("@firstName,");
}
if (paratmeterNames != null && paratmeterNames.Length > 0)
{
paratmeterNamesUpdated = paratmeterNames.ToString().TrimEnd(',');
}
var result = _context.Database.SqlQuery<T>("exec sp_name " + paratmeterNamesUpdated, parameterList.ToArray()).ToList();
//this will make below **calls to SQL DB:**
//exec sp_describe_parameter_encryption N'exec sp_name @lastName,@firstName',N'@lastName varchar(60),@firstName varchar(30)'
//exec sp_executesql N'exec sp_name @lastName,@firstName',N'@lastName varchar(60),@firstName varchar(30)',@lastName=0x01A2256C34F97,@firstName=0x018EE4BD11BA7