Proposal: EF.Parameter(value).OptimizeFor(constantValue)

[arontsang]

What problem are you trying to solve?

Sometimes SQL Server does not generate optimal query plans based on parameter sniffing alone.

By injecting a value for EF to generate a query hint for, we can give SQL Server a better chance to generate optimal queries.

Describe the solution you'd like

Add the fluent api EF.Parameter(value).OptimizeFor(constantValue) which then adds

Option(Optimize For \@p__linq__0 = 'constantValue') into the command text.

[roji]

Unless I'm mistaken, the OPTIMIZE FOR query hint in SQL Server is a query-level hint - appended at the end - rather than something specified on the placeholder within the SQL query; that makes sense as the parameter is sent only once, although it can be referenced multiple times in the SQL query.

As a result, this would already be covered by the general query hint issue #6717.

[arontsang]

@roji it is a query level hint.

However, you would need to correlate the name of the parameter with the supplied constant value. From the C# end, I cannot see a better place to put this.

[roji]

You're right that correlating the parameter name is problematic - though on the other hand expressing this on the parameter has the issue of allowing the user to specify different values for the same parameter (as I wrote above). But I agree what you propose is probably best.

I'll go ahead and open this for the backlog, let's see if other people vote for it.