Getting the SQL for HQL and Criteria Queries

Monday, January 19, 2015

OK, so, I already showed how we can get the SQL that was generated from a LINQ query. Of course, we can do the same for both HQL and Criteria APIs as well (QueryOver is just a wrapper around Criteria, mind you).

So, for HQL (and SQL), it goes like this:

   1: public static class QueryExtensions   2: {   3:     private static readonly PropertyInfo sessionProperty = typeof (QueryImpl).GetProperty("Session", BindingFlags.NonPublic | BindingFlags.Instance);   4:     5:     public static String ToSql(this IQuery query)   6:     {   7:         if (query is QueryImpl)   8:         {   9:             var session = sessionProperty.GetValue(query, null) as ISession;  10:             var sessionImpl = session.GetSessionImplementation();  11:             var translatorFactory = new ASTQueryTranslatorFactory();  12:             var translators = translatorFactory.CreateQueryTranslators(query.QueryString, null, false, sessionImpl.EnabledFilters, sessionImpl.Factory);  13:    14:             return translators[0].SQLString;  15:         }  16:         else if (query is SqlQueryImpl)  17:         {  18:             return (query.QueryString);  19:         }  20:    21:         throw (new NotSupportedException("Query type is not supported."));  22:     }  23: }

You can pass any implementation if IQuery, such as one produced from ISession.CreateQuery() or ISession.CreateSQLQuery(). The static field is merely for performance reasons.

As for Criteria:

   1: public static class CriteriaExtensions   2: {   3:     public static String ToSql(this ICriteria criteria)   4:     {   5:         var criteriaImpl = criteria as CriteriaImpl;   6:         var sessionImpl = criteriaImpl.Session;   7:         var factory = sessionImpl.Factory;   8:         var implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);   9:         var loader = new CriteriaLoader(factory.GetEntityPersister(implementors[0]) as IOuterJoinLoadable, factory, criteriaImpl, implementors[0], sessionImpl.EnabledFilters);  10:    11:         return loader.SqlString.ToString();  12:     }  13: }

And finally, QueryOver, just a small wrapper around the Criteria version:

   1: public static class QueryOverExtensions   2: {   3:     public static String ToSql(this IQueryOver queryOver)   4:     {   5:         var criteria = queryOver.UnderlyingCriteria;   6:         return (criteria.ToSql());   7:     }   8: }