1

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

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;
Tim
  • 8,669
  • 31
  • 105
  • 183
  • I would at least try creating C# type `class IntegerListRow { public int i {get;set;}}` and mapping `List` to `List`. Though I don't have an appropriate setup right now to test. – Guru Stron Mar 20 '23 at 19:19
  • @Guru Stron: Yes, I'm trying alternatives. DataTable worked, though not at first. You have to be careful not to fully qualify the TypeName when defining the Command parameter. ADO.NET wants `param.TypeName= "dbo.MyType"` not "MYDB.dbo.MyType". – Tim Mar 20 '23 at 19:32
  • _"ADO.NET wants `param.TypeName= "dbo.MyType"` not `"MYDB.dbo.MyType"`. "_ - this kind of makes sense since you are already have connected to the database. – Guru Stron Mar 20 '23 at 19:51
  • @Guru Stron: I can't get the `List` to work. Not sure if I'm doing something wrong or it's just not supported. ADO.NET throws an error when it is sent a `List`. – Tim Mar 21 '23 at 13:28
  • Then use your approach with Datatable – Guru Stron Mar 21 '23 at 13:32
  • Now I know why you're called "Guru" Stron, oh wise one. – Tim Mar 21 '23 at 15:10
  • Hahahhaha, nice one!))) – Guru Stron Mar 21 '23 at 15:14
  • BTW fell free to post and accept your answer, since it seems you have solved the issue) – Guru Stron Mar 21 '23 at 15:15
  • I made this for something different but maybe can help you with some ideas: https://stackoverflow.com/a/69574353/888472 and instead of a datatable you can reach a List or IEnumerable – Leandro Bardelli Mar 27 '23 at 00:14

0 Answers0