How to pass List<Int> to SQL Server stored procedure

Question

I have this user table type in SQL Server:

CREATE TYPE [dbo].[ListNew] AS TABLE
                               (
                                    [Id] [int] NOT NULL,
                                    PRIMARY KEY CLUSTERED ([Id] ASC) WITH (IGNORE_DUP_KEY = OFF)
                               )
GO

And use this type in stored procedure parameter:

....
    (@lstNew ListNew READONLY,
     @UserName nvarchar(128))
AS
....

And using this stored procedure in ASP.NET MVC with this code:

List<int> lstNew = MyList.Select(o => o.Key).ToList();
List<XXXView> lstView = db.Database.SqlQuery<XXXView>("MyStoredProcedure @lstNew,@UserName",
              new SqlParameter("@lstNew", lstNew),
              new SqlParameter("@UserName", userName)).ToList();

but it's not working and get this error:

No mapping exists from object type System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

I try without ListNew and used only username, it's working

---

Edit:

I use this code:

myParameter.SqlDbType = SqlDbType.Udt;
myParameter.UdtTypeName = "ListNew";

But I get the same warning

Answer 1

This is a solved problem and properly documented in - cough - the documentation.

YOu will need to define the table on the server side and then can pass in a table valued parameter.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

This runs down to using the SqlDbType Structured.

// Configure the command and parameter.  
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.CategoryTableType";

You CAN use a DataTable, but then you introduce the most overhead approach possible as object model - or you just use...

https://forums.asp.net/t/1845039.aspx?Table+Value+Parameter+Use+With+C+without+using+DataTable

Basically you transform your data into SqlDataRecords and pass them in. Needs some metadata - but generally this can be generalized and fits in below a page of code. The link has the code (which I can not copy here due to - well - it not being MY code).