How to use Table-Values parameter with EF Core?

We have custom type in SQL Server database

CREATE TYPE dbo.UniqueId AS TABLE (CategoryID 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

var identifiers = new[] { Guid.NewGuid(), Guid.NewGuid() };  var orders = 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 syntax) 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?

https://stackoverflow.com/questions/67352165/how-to-use-table-values-parameter-with-ef-core May 02, 2021 at 09:06AM