Feature Request: LINQ to table parameter #13239
Description
Use Case: Ability to use query against a variable number of inputs, for a batching scenario. Ideally I'd like to have the same query plan used whether there are 2 input values or 100 input values, in conjunction with a linq query.
Hypothetical linq:
public class MyDbContext : DbContext
{
// ... or however it makes sense to define a udt
public DbParameter<IdUdt> IdTable { get; set; }
}
public class IdUdt : DbTableParameter<long>
{
// not sure if this would be necessary, but could enable the With function below
protected override void Populate(long value)
{
this.Id = value;
}
public long Id { get; set; }
}
public static class Execution
{
public static async Task<List<Person>> GetWithTableParameterAsync(IEnumerable<long> ids, CancellationToken cancellationToken)
{
using (var db = new MyDbContext()) {
var query = from person in db.People
join id in db.IdTable.With(ids) on person.PersonId equals id.Id
select person;
return await query.ToListAsync(cancellationToken);
}
}
}Ideally this generates t-sql that looks something like:
declare @p0 dbo.udt_Id
insert into @p0 values(1)
insert into @p0 values(2)
insert into @p0 values(3)
insert into @p0 values(4)
exec sp_executesql N'SELECT
[Extent1].[PersonID] AS [Id],
[Extent1].[Name] AS [Name]
FROM [dbo].[People] AS [Extent1]
INNER JOIN @p0 i ON t.[PersonID] = i.[ID]',N'@ids [dbo].[udt_TableBigintId] READONLY',@ids=@p0
Note that I realize I can already do something similar with a Contains -- something like:
public static async Task<List<Person>> GetWithInClauseAsync(IEnumerable<long> ids, CancellationToken cancellationToken)
{
using (var db = new MyDbContext()) {
var query = from person in db.People
where ids.Contains(person.PersonId)
select person;
return await query.ToListAsync(cancellationToken);
}
}The difference is in the sql that gets generated, since it embeds the id values into the query (or if using an expression tree walker, I believe it's possible to change this to have n equality checks with sql parameters). I'd prefer a single query plan to either of these solutions.
I also realize that it's possible to use table parameters with raw sql, but the challenge there is that I can't easily inject it into the middle of a complex query if I want to use LINQ.