How to force entity framework not to query as sp_executesql

Question

I have a table with 10 bilions of rows. When I query with where clausule and a value is hardcoded ( tabulka = tabulka.Where(x => x.Value.Contains("value") the sql query is send to sql as batch. It tooks about 5 seconds. When the value is not hardcoded ( tabulka = tabulka.Where(x => x.Value.Contains(value) ), the query is send a RPC and it tooks 15 seconds.

This is real example what EF sends to SQL ( from Profiler ):

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Tabulka] AS [Extent1]
    WHERE ([Extent1].[Column] LIKE @p__linq__0 ESCAPE N''~'') AND ([Extent1].[Column] LIKE @p__linq__1 ESCAPE N''~'')
)  AS [GroupBy1]',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'%text1%',@p__linq__1=N'%text2%'

This takes 15 seconds.

When I add OPTION (RECOMPILE) it tooks 5 seconds:

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Tabulka] AS [Extent1]
    WHERE ([Extent1].[Column] LIKE @p__linq__0 ESCAPE N''~'') AND ([Extent1].[Column] LIKE @p__linq__1 ESCAPE N''~'')
)  AS [GroupBy1] OPTION (RECOMPILE)',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'%text1%',@p__linq__1=N'%text2%'

It also takes 5 seconds if I rewrite it to simple query:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Tabulka] AS [Extent1]
    WHERE ([Extent1].[Column] LIKE '%text1%' AND [Extent1].[Column] LIKE '%text2%')
)  AS [GroupBy1]

Question is how force EF to send it as Batch or do something to take 5 seconds instead of 15.

These queries are only as a demo, the real query can be much more complicated and I dont what to rebuild it not to use IQueryable.

Any help will be appriciated. Here are execution plans for queries

Answer 1

I can think of a few ways to get around it but as of EF 6.2 and earlier there is no standard/built in way to change how EF creates the query to add hints/options. The problem you are facing is due to an in-efficient query plan that is being persisted in the statistics and is being re-used when you call it with another value. I would first try to figure why the bad query plan is being created and maybe there is a better way to get around it than manipulate the code. To do this try asking the question (minus the EF part) in a Ms Sql forum and include the queries, schema details, and your execution plans like you did in this post.

Please also check your mapping if your nvarchar/varchar column is set for the correct encoding (ascii/unicode) in your DbContext mappings.

Optional work arounds

1. Build the query in the traditional/old way using a string builder. Once you have the query execute it on a DbSet on the DbContext using SqlQuery function. This gives you complete control over the build up of the query and what is included but with obvious draw backs like not being able to use Linq-to-Sql for query build up.
2. Create a stored proc and map this to EF. In the stored proc you can build your queries how you want. The result of the stored proc could be one or more side effects (also a table if I am not mistaken) and mapped back to an object using EF.
3. Hook into the DbContext and catch the built sql before it is executed and then manipulate the Sql to add your option statement(s). You can do this with a custom implementation of the IDbCommandInterceptor interface and then re-write the sql to include the OPTION statement to re-generate the execution plan. I would use this as a last resort.