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` 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= 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 parse…

Accepted Answer

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

Using DataTable as a Table Valued Parameter in EF Core 2.0

Solution

Problem