在EF Core 2.0中将DataTable用作表值参数 [英] Using DataTable as a Table Valued Parameter in EF Core 2.0

问题描述

[更新的问题描述]
我们有一个批量导入过程，我们正在将 IEnumerable< SqlDataRecord> 作为表值参数（TVP）传递给直到EF Core 1.1才提供存储为 DataTable 类型的Proc。我们刚刚将项目升级为使用.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);

这篇关于在EF Core 2.0中将DataTable用作表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！