Catching the NHibernate generated SQL and amending before running

Question

Is it possible to obtain the sql that would be created by nhibernate in your code without actually running it?

I have a complex criteria object that I have built through the criteria API. This criteria object forms the base of various select statements. I can then take this base and add on the additional criteria I require in differing scenarios throughout my application.

I now have the need to add a having clause to one of my select statements and apparently this is not an option using the criteria api. I can create the projection I require and if I view the sql generated all I need to add to the bottom of the existing criteria is...

HAVING SUM(J.HoursAssigned) <> sum(JTB.HourQty)

Its very frustrating to be so close but I cannot seem to get one extra line to tag onto the bottom of the generated SQL.

I am thinking that if I could extract the generated SQL I could then tag on the having clause that I need and I could submit the whole thing through the Nhibernate SQLQueryCriteria.

I know its not ideal but this would seem better to me than having one query written in HQL or SQL when the rest share a common criteria base.

Is this a possible and is it a good idea? Any alternatives would also be welcomed.

Krahmer

Related question (hibernate rather than nhibernate): #554981 – Simplistic

Answer 1

In the post mentioned in the comment above I found the following snippet of code which works great.

public static string GenerateSQL(ICriteria criteria)
    {
        NHibernate.Impl.CriteriaImpl criteriaImpl = (NHibernate.Impl.CriteriaImpl)criteria;
        NHibernate.Engine.ISessionImplementor session = criteriaImpl.Session;
        NHibernate.Engine.ISessionFactoryImplementor factory = session.Factory;

        NHibernate.Loader.Criteria.CriteriaQueryTranslator translator = 
            new NHibernate.Loader.Criteria.CriteriaQueryTranslator(
                factory, 
                criteriaImpl, 
                criteriaImpl.EntityOrClassName, 
                NHibernate.Loader.Criteria.CriteriaQueryTranslator.RootSqlAlias);

        String[] implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);

        NHibernate.Loader.Criteria.CriteriaJoinWalker walker = new NHibernate.Loader.Criteria.CriteriaJoinWalker(
            (NHibernate.Persister.Entity.IOuterJoinLoadable)factory.GetEntityPersister(implementors[0]),
                                translator,
                                factory,
                                criteriaImpl,
                                criteriaImpl.EntityOrClassName,
                                session.EnabledFilters);

        return walker.SqlString.ToString();
    }

Krahmer

The issue with this code is that it doesn't put parameters inline with the query, you only see question marks. – Metalanguage