Pass table value type to SQL Server stored procedure via Entity Framework




I created a user-defined table type in SQL Server:



```
CREATE TYPE dbo.TestType AS TABLE 
(
    ColumnA int,
    ColumnB nvarchar(500)
)

```

And I'm using a stored procedure to insert records into the database:



```
create procedure [dbo].[sp_Test_CustomType]
   @testing TestType READONLY
as
    insert into [dbo].[myTable]
        select ColumnA, ColumnB 
        from @testing

```

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?


I tried adding the stored procedure to the model, but I'm unable to find the desired stored procedure in the updated context.


What I'm trying to do is to execute a bulk insert to a table, here's the method that I'm currently using:



```
List<items> itemToInsertToDB = //fetchItems;

foreach(items i in itemToInsertToDB)
{
     context.sp_InsertToTable(i.ColumnA, i.ColumnB)
}

```

Currently, I use a `foreach` 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.


So how to solve this problem? or are there any better ways to do this?
Lets say you want to send a table with a single column of GUIDs.


First we need to create a structure using [SqlMetaData](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmetadata(v=vs.110).aspx) which represents the schema of the table (columns).



```
var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

```

Next you create a list of records that match the schema using [SqlDataRecord](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqldatarecord(v=vs.110).aspx).



```
var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

```

Then create the [SqlParameter](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx):



```
var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

```

Then simply call the stored procedure by using the [Database.SqlQuery](https://msdn.microsoft.com/en-us/library/gg696545(v=vs.113).aspx).



```
IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

```

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):



```
CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

```

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):



```
CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

```
`out` parameters in SQL Server are fun. You have to repeat yourself *everywhere*.


Specifically, here, you've forgotten to mention it in the parameter list when calling the stored proc. `dbo.InsertPhones @model, @out` should be `dbo.InsertPhones @model, @out out`. There may also be a way to omit the parameter list from this string and instruct SQL Server that you're giving it a stored procedure name, not arbitrary query text, but I'm not sure how you would do that specifically here (with an `SqlCommand` object, it would be via the `CommandType` property).


If you're using an `out` parameter, the `select` at the end of the stored procedure is unnecessary, but I assume that was an attempt at a fix. Did you bind the listBox1, using DataSource property from DataTable?  

If so, you have to 1st get the item and value from listBox item and add it some other place (in your case to listBox2):



```
for(int i = 0; i < listBox1.SelectedItems.Count; i++)
{
     DataRowView view = listBox1.SelectedItem as DataRowView;
     string item = view["ColumnNameOfItem"].ToString(); //can be some name
     int value = Convert.ToInt32(view["ColumnNameOfValue"]);  //can be some id (number)
     listBox2.Items.Add(item); // it you want to add item (you can add value as well);
}
```

ColumnNameOfItem (or OfValue) are the names of fields in your dataTable (and same are in dataBase as well). Insteads of names you can use indexes (0 and 1, but without quotation marks).



Hope it helps.  

bye You are mixing [expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression?view=netframework-4.7.1) with [lambda expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression?view=netframework-4.7.1). There are many posts showing how you can combine lambda expressions, but the essential part is to compose expressions from lambda expression [bodies](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.body?view=netframework-4.7.1) and rebind the [parameters](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.parameters?view=netframework-4.7.1).


The later is usually achieved by a custom [`ExpressionVisitor`](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expressionvisitor?view=netframework-4.7.1) like this:



```
using System.Linq.Expressions;

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

```

Now regarding the EF Core combined query filters. 


Using dictionaries and expression lists seems overcomplicated for what are you doing. Since [`IMutableEntityType`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype?view=efcore-2.1) provides read/write access to the [`QueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype.queryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_IMutableEntityType_QueryFilter), the same can be achieved with a small set of a custom extension methods.


All they go inside a class like this:



```
using System;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public static class QueryFilterExtensions
{
}

```

First method:



```
public static void AddQueryFilter(this IMutableEntityType target, LambdaExpression filter)
{
    if (target.QueryFilter == null)
        target.QueryFilter = filter;
    else
    {
        var parameter = target.QueryFilter.Parameters[0];
        var left = target.QueryFilter.Body;
        var right = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        var body = Expression.AndAlso(left, right);
        target.QueryFilter = Expression.Lambda(body, parameter);
    }
}

```

This is a non generic method which combines the exiting filter with passed filter using `AndAlso` (C# `&&`) operator and shows the aforementioned lambda expression combining principles.


However it's not so useful directly, like inside your entity type configuration loop (it can, but requires you to build manually the lambda expression instead of letting C# compiler do that). So here comes the second method:



```
public static void AddQueryFilter<T>(this IMutableEntityType target, Expression<Func<T, bool>> filter)
{
    LambdaExpression targetFilter = filter;
    if (target.ClrType != typeof(T))
    {
        var parameter = Expression.Parameter(target.ClrType, "e");
        var body = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        targetFilter = Expression.Lambda(body, parameter);
    }
    target.AddQueryFilter(targetFilter);
}

```

It's a generic method - not quite type-safe, but allows you to use a compile time lambda expression and bind it to the actual entity type as follows:



```
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
    if (typeof(IDeleted).IsAssignableFrom(entityType.ClrType))
        entityType.AddQueryFilter<IDeleted>(e => e.DateTimeDeleted == null);
}

```

Looks better, isn't it :)


The last custom extension method is complement to (replacement of) the standard EF Core generic [`HasQueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.entitytypebuilder-1.hasqueryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_Builders_EntityTypeBuilder_1_HasQueryFilter_System_Linq_Expressions_Expression_System_Func__0_System_Boolean___) method:



```
public static EntityTypeBuilder<TEntity> AddQueryFilter<TEntity>(this EntityTypeBuilder<TEntity> target, Expression<Func<TEntity, bool>> filter)
    where TEntity : class
{
    target.Metadata.AddQueryFilter(filter);
    return target;
}

```

and allows you to replace



```
this.AddToDict<DbMyEntity>(t => t.Name == null || t.Name == "Something");

```

with the more convenient



```
modelBuilder.Entity<DbMyEntity>()
    .AddQueryFilter(t => t.Name == null || t.Name == "Something");

```

**Update (EF Core 3.0):** `QueryFilter` property has been replaced with `GetQueryFilter` and `SetQueryFilter` extension methods. As I understand, you are following the following approach:



```
modelBuilder.Entity<IdentityRole>().HasData(new IdentityRole { Name = "Admin", NormalizedName = "Admin".ToUpper() });

```

There are two problems with this code.


The first is the usage of `modelBuilder.Entity<IdentityRole>()`. This makes `IdentityRole` class to be treated by EF Core as an *entity*, hence the `IdentityRoles` table it tries to create. It's because `IdentityRole` class is *different* from what your model is using - `IdentityRole<Guid>`. Change it to `modelBuilder.Entity<IdentityRole<Guid>>()` (and also the `new` operator) and the problem will be solved.


The second problem is that `HasData` *requires* PK values to be specified, as explained in the [Data Seeding](https://docs.microsoft.com/en-us/ef/core/modeling/data-seeding) section of the EF Core documentation:



> 
> The primary key value needs to be specified even if it's usually generated by the database. It will be used to detect data changes between migrations.
> 
> 
> 


So additionally to changing the `HasData` generic type argument, you need to pre generate Guid to be used as Id for the new role (see [Entity Framework Core Auto Generated guid](https://stackoverflow.com/questions/53493025/entity-framework-core-auto-generated-guid/53495148#53495148) for more details a the similar issue) and use something like this:



```
modelBuilder.Entity<IdentityRole<Guid>>().HasData(
    new IdentityRole<Guid>
    {
        Id = new Guid("pre generated value"),
        Name = "Admin",
        NormalizedName = "Admin".ToUpper()
    }
);

``` Change this line of code (sql query) into:



```
OleDbCommand cmd = new OleDbCommand(Stirng.Format("INSERT INTO CHILDREN2 (Emp_ID , Child_Name, Age) VALUES ({0}, {1}, {2})", Convert.ToInt32(CaptionText.Text),txtName.Text, txtAge), clsData.con);
```

Do you have a WHERE clause too? I removed it from your query, becasue it didnt belonge on that place where you put it.  

If you will use a Where clause then create an sql query this was:



```
"INSERT INTO TableName (Field1, Field2) VALUES (@paramField1, @paramField2) WHERE Field3 = @paramField3"
```

where params are parameters passed to the fields, and WHERE is a conditional clause, and has to be stated on the end (after specifying parameters). The basic idea here is that your adding properties to the model that EF can use to make a relationship. Right you're trying to create a relationship of type `User` and that is creating an error. To assign a composite key each key needs to be a type compatible with a `Key` field, not a navigation property. So we add `UserId` and `FriendId` of type `int`, `string` or `GUID` etc. and create a relationship off those properties.



```
public class Relationship
{
    public User Friend { get; set; }

    public User User { get; set; }

    public int FriendId { get; set; }

    public int UserId { get; set; }

    public DateTimeOffset RelationshipInitializationDate { get; set; }
}

public class User
{
    public int UserId { get; set; }
}

```

You can now define a composite key across `UserId` and `FriendId`. Something like this should do:



```
public class NorthwindContext : DbContext
{
     public NorthwindContext(DbContextOptions<NorthwindContext> options):base(options) { }

     public NorthwindContext() { }

     protected override void OnModelCreating(ModelBuilder builder)
     {
         builder.Entity<Relationship>().HasKey(table => new {
         table.FriendId, table.UserId
         });
     }
     public DbSet<Relationship> Relationships { get; set; }
     public DbSet<User> Users { get; set; }
}

```

[Source: Medium - How To: Entity Framework Core relationships, composite keys, foreign keys, data annotations, Code First and Fluent API](https://medium.com/radzenhq/how-to-entity-framework-core-relationships-composite-keys-foreign-keys-data-annotations-code-f4b238086463) Dapper just wraps raw ADO.NET; raw ADO.NET doesn't offer a facility for this, therefore dapper does not. What you want is `SqlBulkCopy`. You could *also* use a table-valued-parameter, but this really feels like a `SqlBulkCopy` job.


In a pinch, you can *use* dapper here - `Execute` will unroll an `IEnumerable<T>` into a series of commands about `T` - but it will be lots of commands; and unless you explicitly enable async-pipelining, it will suffer from latency per-command (the pipelined mode avoids this, but it will still be n commands). But `SqlBulkCopy` will be *much* more efficient.


If the input data is an `IEnumerable<T>`, you might want to use `ObjectReader` from FastMember; for example:



```
IEnumerable<SomeType> data = ...
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

``` Since you are storing pointer to type objects why not try using polymorhism by writing a base method called displayPay() declared with the keyword virtual that would be overloaded with in each of the inherited classes. Then you can loop through the vector of pointer to StaffMembers calling the displayPay() method and the correct version of displayPay() will be called for each referenced object. Open SQL Enterprise Manager and expand the Databases tree. Right click on the database name and select All Tasks, then select Detach Database. Move the physical files from the D drive to the H drive. Right click on databases and select All Tasks > Attach Database. Browse to the new location and select the mdf file. Click OK, then OK again (you may need to correct the path to the log file). Using [reflection](/questions/tagged/reflection "show questions tagged 'reflection'") and [expression-trees](/questions/tagged/expression-trees "show questions tagged 'expression-trees'") you can provide the parameters and then call `OrderBy` function, Instead of returning `Expression<Func<Task, T>>` and then calling `OrderBy`.


Note that `OrderBy` is an extension method and has implemented in both `System.Linq.Enumarable` and `System.Linq.Queryable` classes. The first one is for [linq-to-objects](/questions/tagged/linq-to-objects "show questions tagged 'linq-to-objects'") and the latter is for [linq-to-entities](/questions/tagged/linq-to-entities "show questions tagged 'linq-to-entities'"). [entity-framework](/questions/tagged/entity-framework "show questions tagged 'entity-framework'") needs the expression tree of the query in order to translate it to SQL commands. So we use the `Queryable` implementation.


It can be done by an extension method(explanations added as comments):



```
public static IOrderedQueryable<TSource> OrderBy<TSource>(
       this IQueryable<TSource> query, string propertyName)
{
    var entityType = typeof(TSource);

    //Create x=>x.PropName
    var propertyInfo = entityType.GetProperty(propertyName);
    ParameterExpression arg = Expression.Parameter(entityType, "x");
    MemberExpression property = Expression.Property(arg, propertyName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    //Get System.Linq.Queryable.OrderBy() method.
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            //Put more restriction here to ensure selecting the right overload                
            return parameters.Count == 2;//overload that has 2 parameters
         }).Single();
    //The linq's OrderBy<TSource, TKey> has two generic types, which provided here
    MethodInfo genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    /*Call query.OrderBy(selector), with query and selector: x=> x.PropName
      Note that we pass the selector as Expression to the method and we don't compile it.
      By doing so EF can extract "order by" columns and generate SQL for it.*/
    var newQuery = (IOrderedQueryable<TSource>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });
    return newQuery;
}

```

Now you can call this overload of `OrderBy` like any other overload of it.  

For example:



```
var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();

```

Which translates to:



```
SELECT TOP (10)  {coulmn names} FROM  [dbo].[Items] AS [Extent1] 
       ORDER BY [Extent1].[Money] ASC

```

This approach can be used to define all overloads of `OrderBy` and `OrderByDescending` methods to have `string` property selector.