Simple builder class for creating TVPs that work in .NET Core 1.0

MyContext.cs

using Microsoft.EntityFrameworkCore;

namespace TvpSampleApp
{
    public class MyContext : DbContext
    {
        public DbSet<Person> People { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database=TvpSample; Integrated Security=yes");
        }
    }
}

Person.cs

namespace TvpSampleApp
{
    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Program.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.SqlServer.Server;
using System.Data;

namespace TvpSampleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {

            using (var context = new MyContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.Database.ExecuteSqlCommand(@"CREATE TYPE dbo.IdTable AS TABLE (Id Int);");

                context.People.Add(new Person { });
                context.People.Add(new Person { });
                context.People.Add(new Person { });

                context.SaveChanges();

            }

            using (var context = new MyContext())
            {
                var tvp = new TableValuedParameterBuilder("dbo.IdTable", new SqlMetaData("Id", SqlDbType.Int))
                    .AddRow(1)
                    .AddRow(2)
                    .CreateParameter("p0");

                var query = context.People.FromSql("SELECT * FROM People WHERE Id IN (SELECT * FROM @p0)", tvp);

                foreach (var person in query)
                {
                    System.Console.WriteLine(person.Id);
                }
            }
        }
    }
}

TableValuedParameterBuilder.cs

using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace TvpSampleApp
{
    public class TableValuedParameterBuilder
    {
        readonly string _typeName;
        readonly SqlMetaData[] _columns;
        readonly List<SqlDataRecord> _rows;
        
        public TableValuedParameterBuilder(string typeName, params SqlMetaData[] columns)
        {
            _typeName = typeName;
            _columns = columns;
            _rows = new List<SqlDataRecord>();
        }

        public TableValuedParameterBuilder AddRow(params object[] fieldValues)
        {
            var row = new SqlDataRecord(_columns);
            row.SetValues(fieldValues);
            _rows.Add(row);
            return this;
        }

        public SqlParameter CreateParameter(string name)
        {
            return new SqlParameter
            {
                ParameterName = name,
                Value = _rows,
                TypeName = _typeName,
                SqlDbType = SqlDbType.Structured
            };
        }
    }
}

Thank you for your respnse. But I have to pass the table valued parameter to an existing stored procedure. Is it possible to do that? The example shows passing the table valued parameter to an inline query.

Hi, I get following error when i try to use above code with stored procedure receiving table value parameter.
System.InvalidOperationException: 'No mapping to a relational type can be found for the CLR type 'TableValuedParameterBuilder'.'
any solution?

That error is because you are passing the actual TableValuedParameterBuilder into the query instead of the sql parameter. The CreateParameter method returns a sql parameter, which is what the example is passing into the query. Make sure that you are passing in the output of CreateParameter into your query and not the builder itself.