I am trying to send an IEnumerable<int>
to a stored proc on the server, one of whose parameters is a user-defined table type, and I keep getting an error that the value I'm supplying to the parameter cannot be converted to an IEnumerable<int>
.
{"Failed to convert parameter value from a List1 to a IEnumerable
1."}
My user-defined table type on the server is dead simple:
CREATE TYPE [dbo].[IntegerList] AS TABLE(
[i] [int] NULL
)
and the Microsoft docs say an IEnumerable<T>
can be used with these table-types via System.Data.SqlClient, which is the library I'm using, not Microsoft.Data.SqlClient:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable T \ SqlDataRecord objects.
So, how to convert a List<int>
to an IEnumerable<int>
in a manner that System.Data.SqlClient finds acceptable? There are many questions about this conversion, but nothing seems to work for me.
I have tried casting myList to IEnumerable<int>
but that doesn't satisfy SqlClient, as the same error occurs when I assign the parameter value with variable enumerable
:
IEnumerable<int> enumerable = (IEnumerable<int>)myList;
param2.SqlDBType = SqlDbType.Structured;
param2.TypeName = "MYDB.dbo.IntegerList"; // user-defined table-type
param2.Value = enumerable;