Add SQL query options to NHibernate query

Question

I'm working with some code that generates ICriteria queries for NHibernate. They get executed using ActiveRecord and ActiveRecordMediator.FindAll()

For certain queries, I need to optimise them by adding on an OPTION (HASH JOIN) hint at the end of the SELECT statement. Is there a quick way I can do this?

I don't want to rewrite the queries using plain SQL. Well, I'd love to, but they're too complicated. But if, for example, there's an easy way I can trap and modify the SQL before it's pumped off to SQL Server, I'd be happy.

Answer 1

Implement an IInterceptor and do your modifications in OnPrepareStatement(). Then pass your interceptor to ISessionFactory.OpenSession().

Here's an example.

Or you could try registering a custom function in your dialect. (example)