Using DataTable as a Table Valued Parameter in EF Core 2.0

Question

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

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?

Are you saying this did work with EF Core 1.1 ? Better be clear about the parts of the problem. — Henk Holterman
– Henk Holterman, Commented May 14, 2017 at 20:06
The exception suggests it expects a IEnumerable<>, so what if you replace the DataTable with a List<ClassWithCol1Col2> — Henk Holterman
– Henk Holterman, Commented May 15, 2017 at 5:31
I doubt it was ever supported. Check out this answer in MSDN link. I guess the only working option available now is to use IEnumerable<SqlDataRecord>. — Karthikeyan
– Karthikeyan, Commented May 15, 2017 at 6:59
Yes, DataTable with Core 2 surprised me. That's very old tech. — Henk Holterman
– Henk Holterman, Commented May 15, 2017 at 7:09

Peter Csala · Accepted Answer · 2020-10-12 06:50:07Z

9

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.".

I'm using EFCore 2.0.2 and EFCore.SqlServer 2.0.2

This is my code:

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

edited Oct 12, 2020 at 6:50

Peter Csala

23.7k16 gold badges51 silver badges96 bronze badges

answered Aug 15, 2018 at 20:50

user15741

1,41217 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using DataTable as a Table Valued Parameter in EF Core 2.0

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related