title: Pass table value type to SQL Server stored procedure via Entity Framework
body: <p>I created a user-defined table type in SQL Server:</p>
  
  <pre><code>CREATE TYPE dbo.TestType AS TABLE 
  (
      ColumnA int,
      ColumnB nvarchar(500)
  )
  </code></pre>
  
  <p>And I'm using a stored procedure to insert records into the database:</p>
  
  <pre><code>create procedure [dbo].[sp_Test_CustomType]
     @testing TestType READONLY
  as
      insert into [dbo].[myTable]
          select ColumnA, ColumnB 
          from @testing
  </code></pre>
  
  <p>And I would like to use EF to execute this stored procedure, but here's the problem: how can I pass a user defined table to the stored procedure?</p>
  
  <p>I tried adding the stored procedure to the model, but I'm unable to find the desired stored procedure in the updated context.</p>
  
  <p>What I'm trying to do is to execute a bulk insert to a table, here's the method that I'm currently using:</p>
  
  <pre><code>List&lt;items&gt; itemToInsertToDB = //fetchItems;
  
  foreach(items i in itemToInsertToDB)
  {
       context.sp_InsertToTable(i.ColumnA, i.ColumnB)
  }
  </code></pre>
  
  <p>Currently, I use a <code>foreach</code> loop to loop through the list to insert item to DB, but if the list have a lot of items, then there will be a performance issue, so, I'm thinking of passing a list to the stored procedure and do the insert inside.</p>
  
  <p>So how to solve this problem? or are there any better ways to do this?</p>