How to use Table-Values parameter with EF Core?

Question

We have custom type in SQL Server database

CREATE TYPE dbo.UniqueIdentifiers AS TABLE (Identifier UNIQUEIDENTFIER)

Purpose of the type is to use it for queries where we look for the records with provided identifiers.

SELECT * FROM MyRecords WHERE Id IN (@GivenIdentifiers)

We have many queries like above which are called multiple times with different list of identifiers, by giving list of identifiers as a SQL Parameter we are improving performance of the query, because SQL Server will use already compiled sql query instead of compiling new one only because list of identifiers is changed.

Now we are going to use EF Core as unit of work for our application with SQL Server database, but I wasn't able to find a way how we can consume Table-Values parameters with EF Core query syntax.

At this moment we have

public Order[] LoadOrders(params Guid[] identifiers)
{
    return context.Orders.Where(order => identifiers.Contains(order.Id)).ToArray();
}

Which generates sql with "hardcoded" identifiers (see below), generated query will be different when same method is called with different identifiers, which will "force" SQL Server to compile query again, that what we are trying to avoid

SELECT * FROM MyRecords WHERE Id IN ('1234abcd-...', '1234abce-...')

We can still use our original approach by building query "manually", but we want to avoid this at this moment, because we would like to be able to build different queries by joining other tables "on the fly" (using EF Core query extension methods) without explicitly introducing separate "building query" for every table where we use similar 'WHERE' condition.

How we can use Table-Valued parameters with EF Core?

Note that [Dapper supports this directly](https://stackoverflow.com/questions/8388093). — Robert Harvey, May 02 '21 at 01:12
Does this answer your question? [Entity Framework Stored Procedure Table Value Parameter](https://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter) You basically need to do direct SQL queries (or `exec` if you have a stored procedure) using `Execute` — Charlieface, May 02 '21 at 01:13
@Charlieface, this is not the similar question. Question you marked as similar is about Entity Framework which is different framework than EF Core, Question you marked as similar is about passing values to the stored procedure, where this question about using it with the EF query syntax. — Basin, May 02 '21 at 01:19
100% sorry I missed that. But the solution is the same, you need `FromSqlRaw` or `FromSql` depending on version, with a direct query — Charlieface, May 02 '21 at 01:21
@RobertHarvey, I agree with OP, "duplicated" question have little similarity with the question here. Of course answer to the question could be simple "No, you can not use it with EF syntax", but maybe customize SQL generation — Fabio, May 02 '21 at 01:22
@Charlieface, OP is aware of the raw sql approach _We can still use our original approach by building query "manually"_ — Fabio, May 02 '21 at 01:22
@Fabio Then on that basis the question is unanswerable, other than saying "You can't" — Charlieface, May 02 '21 at 01:24
@Fabio And what exactly does mean *"EF Core query syntax"*? There is no such term AFAIK - there are interchangeable query and method (and mixed) LINQ syntax, EF Core has nothing to do with that. Passing non CLR parameters requires raw SQL, and raw SQL in EF Core is [composable](https://docs.microsoft.com/en-us/ef/core/querying/raw-sql#composing-with-linq), thus can be combined with/embedded in LINQ syntax. Also it's unclear what they mean by "original approach". So, while the question is not duplicate, it definitely needs clarification, and could be resolved with link to the documentation. — Ivan Stoev, May 02 '21 at 06:53
@IvanStoev: EF Linq has always been a little different from Linq to Objects. Some functions that normally would work under Linq to Objects are not supported in EF. — Robert Harvey, May 02 '21 at 12:35
@RobertHarvey I'm watching/answering in that area for a quite long time, thus I'm aware of the runtime differences you are mentioning. But, the accent in my previous comment was on the word "syntax", and there is no difference in syntax for EF Core / Linq to Entities vs any other queryable LINQ. Some specific methods - yes, translatable and non translatable standard methods - yes. But syntax is one and the same. — Ivan Stoev, May 02 '21 at 12:59
@IvanStoev, sorry for the confusion, I wasn't sure how to describe extension methods provided by EF Core. — Basin, May 09 '21 at 01:51

score 1 · Answer 1 · edited Jun 26 '23 at 12:53

For us was important a possibility to chain other LINQ operators to the Where condition.

@IvanStoev thank you for the link Composing with LINQ, based on composability we were able to use below implementation

-- Create Table-Valued parameter type in database
CREATE TYPE Ids AS TABLE (Id INT);

// Extension method
public static IQueryable<Order> ContainsIds(this IQueryable<Order> source, params int[] ids)
{
    var parameter = ids.ToIdTypeSqlParameter();
    var sql = $"SELECT * FROM [Order] o WHERE EXISTS (SELECT 1 FROM {p.ParameterName} i WHERE i.Id = o.Id)";
    return source.FromSql(sql, parameter);
}

Usage:

var result = await context.Orders
    .ContainsIds(1, 2, 3, 4) 
    .Where(order => order.CreatedAt > 20.January(2020))
    .Select(order => new { order.Id, order.Name, order.TotalPrice })
    .ToArrayAsync();

With current EF Core 6, it would look like this: `.FromSqlInterpolated($"SELECT * FROM [Order] o WHERE EXISTS (SELECT 1 FROM {parameter} i WHERE i.Id = o.Id)")` — CWagner, Oct 18 '22 at 09:16
What is to ToIdTypeSqlParameter() method? What exactly does it return? — Eric, Dec 02 '22 at 14:57

Ross Bush · Answer 2 · 2021-05-09T02:29:58.537

EFCore supports a structured parameter which can be used to serialize a DataTable as a User Defined Table Value. There is a limit to how many values are supported by the IN() statement. To get around that limit, at times, I transport a data table as a User Defined TVP.

  var myDataTable = FillDataTableWithData();
  var paramTable= new SqlParameter
  {
       ParameterName = '@MyIntIDTable',
       SqlDbType = SqlDbType.Structured,
       Value = myDataTable
  }
  await _dbContext.Database.ExecuteSqlRawAsync("BulkValidation {0}", paramTable);

How to use Table-Values parameter with EF Core?

2 Answers2

Linked