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?

Answer 1

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);
}

public static SqlParameter ToIdTypeSqlParameter(
    this IEnumerable<Guid> identifiers,
    string parameterName
)
{
    return new SqlParameter
    {
        ParameterName = parameterName,
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.MyIdentifiersType",
        Value = identifiers.ToDataTable()
    };
}

public static DataTable ToDataTable(this IEnumerable<Guid> identifiers)
{
    var table = new DataTable();
    table.Columns.Add("Id", typeof(Guid));
    foreach (var id in identifiers)
    {
        var row = table.NewRow();
        row.SetField("Id", id);
        table.Rows.Add(row);
    }

    return table;
}

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();