Is there a way to preview the SQL NHibernate generates, without actually running the query.
Something like this:

string sql =
session.CreateCreteria(typeof(Person)).PreviewSQL;


I have a customer asking for this feature,
So he could calculate the sql cost before deciding whether or not to execute the query.

Have you tried Aydene's query analyzer?

http://www.ayende.com/projects/nhiberna ... lyzer.aspx

Ayende's NHibernate Query Analyzer is for HQL (IQuery) not ICriteria.

My question here is why does your customer want "calculate the sql cost before deciding whether or not to execute the query"? Is it that they don't trust the SQL that NHibernate generates?

The purpose in this case is to know whether to allow the end user to execute the query. Oracle database has the feature for calculating the cost of a query. They use the Criteria to build a query dynamically. In some cases (usually in the case of join between large tables) they decide not to allow the user to execute the query.

I believe there could be other cases where this could be useful.

In Oracle the query cost is relative (to the other possible execution plans for the SQL statement), so I don't think that it's going to help you. However it's been several years since I did any serious Oracle development and things might have changed. I suggest a posting here http://asktom.oracle.com to answer that part of the question.

I know of a trick to extract the SQL for IQuery: http://elegantcode.com/2007/11/16/nhibernate-iquery-to-isqlquery-black-magic/ but I don't know an equivalent for ICriteria (I'm sure there is one if you dig into the internals).

Note that even if you could obtain the SQL to calculate the absolute cost you would still need to submit the SQL to the database engine. So there's going to be two round trips for every query unless you cache the "executability" (and there's enough commonality across queries to justify caching).

I can see the potential need for what you ask but it's very much an edge case. I would be wary about spending a lot of time on this unless you have a concrete (not anticipated) business need (avoid premature optimization!)

I can see your point, but it’s hard for me to estimate if there’s indeed a concrete business need. According to my customer, this is a very high scale database and the user is given the option to query every entity in the system, and to cross it (join select) with every other entity. I can assume that by experimenting they hope to find the cost limit in which the system will decline the user query.

Though that may seem as a crazy mechanism, it does seems fairly reasonable to me to allow previewing the query before executing it. There might be some other situations where I can find this helpful. Maybe even in the case of manipulating the query before the execution.