Skip to content

Instantly share code, notes, and snippets.

@divega divega/MyContext.cs
Last active May 31, 2019

Embed
What would you like to do?
Simple builder class for creating TVPs that work in .NET Core 1.0
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");
}
}
}
namespace TvpSampleApp
{
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
}
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);
}
}
}
}
}
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
};
}
}
}
@AVin9

This comment has been minimized.

Copy link

commented Mar 10, 2017

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.

@pragneshmpatel

This comment has been minimized.

Copy link

commented Jul 17, 2017

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?

@tburkholder

This comment has been minimized.

Copy link

commented Dec 15, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.