I'm having SQL performance issues on Azure with complex EF6 queries. The environment is configured as an elastic pool. In the development area, the eDTU limit is hit frequently via CPU usage which I have attributed to query plan generation.
The application is rather generic, so the EF queries are quite complex, but in the bowels of the queries there is often a is often a subquery that has an IN clause.
SELECT Id FROM Event E WHERE E.Name IN (@p__linq__1, @p__linq__2, @p__linq__3)
OR
SELECT Id FROM Event E WHERE E.Name IN (@p__linq__4, @p__linq__5)
Although the rest of the query is identical, a new sql query plan is generated because the text is different due to the number of parameters.
Previously, in another project, I had increased plan reuse by using a TVP in a join instead of the where clause.
SELECT Id FROM Event E INNER JOIN @p_tvp_strings T ON E.Name = T.[Value]
I can't work out how to do this in EF in an IQueryable way.
I have already tried to create a Datatable and parse that as a parameter but context.Database.SqlQuery doesn't return an IQueryable. It executes the command immediately.
var nameArray = new string[]
{
"Bob", "Fred", "Bill",
};
var nameTable = CreateDataTable(nameArray);
var parameter = new SqlParameter("tvp", nameTable);
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "StringSet";
var names = context.Database.SqlQuery<string>("SELECT [Value] FROM @tvp", parameter).AsQueryable();
var people = context.People.Where(p => names.Contains(p.Name)).ToList();
Unfortunately this results in two queries being generated.
declare @p3 dbo.StringSet
insert into @p3 values(N'Bob')
insert into @p3 values(N'Fred')
insert into @p3 values(N'Bill')
exec sp_executesql N'SELECT [Value] FROM @tvp',N'@tvp [StringSet] READONLY',@tvp=@p3
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Title] AS [Title],
[Extent1].[Age] AS [Age]
FROM
[dbo].[People] AS [Extent1]
WHERE
[Extent1].[Name] IN (N'Bill', N'Bob', N'Fred')
Does anyone know how to use TVP in WHERE clauses when using EF?
