How to append sql table hints to expressions in ormlite?

ServiceStack.OrmLite
1

Hi!

I would need to append “OPTION (RECOMPILE)” to some of the queries.
Is there a way to append table hints to the existing expression?

[some info about the issue][1]
[1]: https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/

2

There wasn’t an official API for this but I’ve just added support for q.SqlFilter or q.WithSqlFilter() in this commit which will let you customize the Sql with:

var q = db.From<Person>()
    .Where(x => x.Age == 27)
    .WithSqlFilter(sql => sql + "\noption (recompile)");

var rockstars = db.Select(q);

This change is available from v4.5.5 that’s now available on MyGet.

Before this release you could modify the q.HavingExpression to achieve a similar result which will append the custom SQL expression to the end of the SQL Statement (i.e. in-place for where the HAVING sql expression would be), e.g:

var q = db.From<Person>()
    .Where(x => x.Age == 27);

q.HavingExpression = "option (recompile)";

var rockstars = db.Select(q);