在 EF Core 2.0 中使用 DataTable 作为表值参数

本文介绍了在 EF Core 2.0 中使用 DataTable 作为表值参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

[更新问题描述]我们有一个批量导入过程，我们将 IEnumerable 作为表值参数 (TVP) 传递给存储过程，因为 DataTable 类型在 EF 之前不可用核心 1.1.我们刚刚升级了我们的项目以使用 .Net Core 2.0 并开始更新代码以使用 DataTable.ExecuteSqlCommandAsync 命令开始抛出 InvalidCastException.以下是异常详情:

[Updated problem description] We have a bulk import process for which we were passing IEnumerable<SqlDataRecord> as a Table Valued Parameter (TVP) to a Stored Proc, as DataTable Type was not available until EF Core 1.1. We just upgraded our project to use .Net Core 2.0 and started updating the code to use DataTable. The ExecuteSqlCommandAsync command started throwing an InvalidCastException. Here are the exception details:

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.

这就是我调用存储过程的方式:

This is how I am calling the stored proc:

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);

随着 DataTable 现在在 EF Core 2.0 中可用，我仍然无法使用它作为 TVP 传递给存储过程.是因为它还不支持还是可能是一个错误?

With DataTable now available in EF Core 2.0, I still can't use it to pass as a TVP to a Stored Proc. Is it because its not supported yet or may be a bug?

推荐答案

我在您的代码中发现的唯一可能错误是 SqlParameter 的 TypeName.您的不是完全合格的，我需要包含架构，即dbo".

The only possible error I could find in your code is the SqlParameter's TypeName. Yours is not fully qualified, I needed to include the schema, i.e. "dbo.".

我使用的是 EFCore 2.0.2 和 EFCore.SqlServer 2.0.2

I'm using EFCore 2.0.2 and EFCore.SqlServer 2.0.2

这是我的代码:

DataTable table = new DataTable(); table.Columns.Add(new DataColumn("FieldId", typeof(int))); table.Columns.Add(new DataColumn("Value", typeof(double))); foreach (Value v in NewRows) { DataRow row = table.NewRow(); row["FieldId"] = v.FieldId; row["Value"] = v.Value; table.Rows.Add(row); } var param = new SqlParameter("@replacementValues", table) { TypeName = "dbo.CustomSqlType", SqlDbType = SqlDbType.Structured }; await _dbContext.Database.ExecuteSqlCommandAsync("EXEC dbo.UpdateValues @replacementValues", param);