Add support for OPTION hints on queries

[andreminelli]

I would like to add hints like OPTION (RECOMPILE). which comes on the end of queries.

https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

I couldn't find a way to do this. Are there any plans to support it? If not, any suggestion on how this could be modeled in the DSL (for a possible PR) ?

[naergaga]

I do it this way.

var sql = result.Sql;
sql += " OPTION (OPTIMIZE FOR UNKNOWN)";
var pars = result.NamedBindings;
return conn.Query<T>(sql, pars);

[andreminelli]

I do it this way.

var sql = result.Sql;
sql += " OPTION (OPTIMIZE FOR UNKNOWN)";
var pars = result.NamedBindings;
return conn.Query<T>(sql, pars);

Nice workaround, thanks!

But it would be good having a supported way of doing this...

[jjamid]

Did anything happen with this feature? I'm using this ugly workaround which doesn't seem very safe, use the last order by to add the option as such:

query.OrderByRaw($"{columnName.HSqlEncode()} OPTION(RECOMPILE)");

[ahmad-moussawi]

Thinking of something like this (as initial thought)

db.Query().AppendRaw("OPTION ....");

or by providing a transform method like this one

db.Query().Transform(sql => sql + " OPTION")

WDYT?

[jjamid]

Thinking of something like this (as initial thought)

db.Query().AppendRaw("OPTION ....");

or by providing a transform method like this one

db.Query().Transform(sql => sql + " OPTION")

WDYT?

I think that the first option is more flexible and more intuitive as it is closer to how we add to the query regularly. But maybe the Transform idea can be useful for other scenarios.