Issue when passing a DataTable to SQL stored procedure containing a table-valued parameter

Question

I can't seem to figure out why I can't get my code below working. I confirmed that my DataTable has records when calling the stored procedure, however the actual table-valued parameter that arrives at my stored procedure has no records. (this was confirmed by logging a rowcount in the proc). Any ideas as to what might be my issue? I'm using EntityFrameworkCore 6.

public class MyDbContext : DbContext, IMyDbContext
{

    public MyDbContext()
    {
    }

    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }

    public int InsertStagingData(DataTable data)
    {
        var parameter = new SqlParameter("@TVP_StagingData", data)
        {
            TypeName = "dbo.dataType",
            SqlDbType = SqlDbType.Structured
        };

        var response = this.Database.ExecuteSqlRaw("EXEC [dbo].[sp_InsertStagingData]", parameter);
        return response;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

Answer 1

Cannot see your type in SQL but here I will go with a simplified example of a list of strings. Somewhat pseudo code here since I am just typing this...

  List<string> coolStrings = new List<string>{"one","two","three","redfish","bluefish"};
  var coolParam = new SqlParameter { ParameterName = "@coolStrings ", SqlDbType = SqlDbType.Structured, Direction = ParameterDirection.Input, Value = coolStrings, TypeName = "dbo.StringList" };
  var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };

  this.Database.ExecuteSqlCommand("EXEC @procResult = [dbo].[sp_InsertStagingData] @coolStrings", coolParam , procResultParam);
  return (int) procResultParam.Value;

Just for clarity a simple type SQL

CREATE TYPE [dbo].[StringList] AS TABLE (
    [val] VARCHAR (1000) NOT NULL);