What is the recommendation on using NHibernate CreateSQLQuery?

My gut tells me that advanced NHibernate users would be against it and I have been looking for actual analysis on this and have found nothing, I'd like for the ans开发者_开发问答wer to address these questions:

What are the pros/cons of using it? Are there any performance implications, both good or bad (e.g. use it to call stored procedures?) In which scenarios should we use/avoid it? Who should use/avoid it?

basically, what are the reasons to use/avoid it and why?

CreateSQLQuery exists for a reason, which is executing queries that are either:

• Not supported
• Hard to write

using any of the other methods.

Of course it's usually the last choice, because:

• It's not object oriented (i.e. you're back to thinking of tables and columns instead of entities, properties and relationships)
• It ties you to the physical model
• It ties you to a specific RDBMS
• It usually forces you to do more work in order to retrieve entities
• It doesn't automatically support features like paging

But if you think it's needed for a particular query, go ahead. Make sure to learn all the other methods first (HQL, Linq, QueryOver, Criteria and Get) to avoid doing unnecessary work.

One of the main reasons to avoid SQL and use HQL is to avoid making the code base dependent on the RDBMS type (e.g. MySQL, Oracle). Another reason is that you have to make your code dependent on the table and column names rather than the entity names and properties.

If you are comparing raw SQL to using the NHibernate LINQ provider there are other compelling reasons to go for LINQ queries (when it works), such as type safety and being able to use VS reference search to determine in what queries a certain table or column is referenced.

My opinion is that CreateSQLQuery() is a "last way out" option. It is there because there are things you cannot do with the other NHibernate APIs but it should be avoided since it more or less goes against the whole idea of using NHibernate in the first place.