[更新问题描述]
我们有一个批量导入流程,我们正在通过该流程IEnumerable<SqlDataRecord>
作为存储过程的表值参数 (TVP),如DataTable
类型直到 EF Core 1.1 才可用。我们刚刚将项目升级为使用 .Net Core 2.0,并开始更新代码以使用 DataTable。这异步执行SqlCommand命令开始抛出一个InvalidCastException
。
以下是异常详细信息:
System.InvalidCastException occurred
HResult=0x80004002
Message=Failed to convert parameter value from a DataTable to a IEnumerable`1.
Source=<Cannot evaluate the exception source>
StackTrace:
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
at System.Data.SqlClient.SqlParameter.GetCoercedValue()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(AsyncCallback callback, Object stateObject)
at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
at System.Threading.Tasks.TaskFactory`1.FromAsync(Func`3 beginMethod, Func`2 endMethod, Object state)
at System.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__26.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.<ExecuteSqlCommandAsync>d__11.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at X.Y.Repositories.Repository.<Import>d__4.MoveNext() in Repository.cs:line 95
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at X.Y.Services.ImportService`2.<BulkImportAsync>d__6.MoveNext() in ImportService.cs:line 70
Inner Exception 1:
InvalidCastException: Object must implement IConvertible.
这就是我调用存储过程的方式:
var dataTable = new DataTable();
dataTable.Columns.Add("Col1", typeof(String));
dataTable.Columns.Add("Col2", typeof(String)); //and so on
foreach (var record in records)
{
var row = dataTable.NewRow();
SetStringValue(row, "Col1", record.Field1); //SetStringValue is just a helper method that assigns DBNull.Value if the field value is null.
SetStringValue(row, "Col2", record.Field2); //and so on
dataTable.Rows.Add(row);
}
var param = new SqlParameter("@Records", dataTable)
{
TypeName = "TVPRecords",
SqlDbType = SqlDbType.Structured
};
await _dbContext.Database.ExecuteSqlCommandAsync("EXEC dbo.ImportData @Records", param);
With DataTable
现在在 EF Core 2.0 中可用,但我仍然无法使用它作为 TVP 传递到存储过程。是因为尚不支持还是可能存在错误?