http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660The right way to proceed in my opion is: try to express the query in HQL or CRiteria, then if you cannot find a way to express your query in this way, try to see around the web if someone already solved a similar problem. Then if still you do not come to a solution try to ask on the forum, and if you still does not have a solution, maybe you really need SQL. Quite often people are skilled with SQL and less with HQL, so they take another way, they do not have idea how to express the query in HQL, but they knows well how to create SQL query, so they simply resort to relay on the language they knows better (SQL), instead of using HQL alk. http://ayende.com/3495/nhibernate-worst-practice-sql#comment8http://ayende.com/3495/nhibernate-worst-practice-sql#comment8Fri, 08 Aug 2008 09:50:41 GMTOops, I mean ON constraints. http://ayende.com/3495/nhibernate-worst-practice-sql#comment7http://ayende.com/3495/nhibernate-worst-practice-sql#comment7Thu, 07 Aug 2008 19:40:53 GMTBunter said it exactly right. We've needed to create custom stored procedures MANY times because of our legacy database. And sometimes you just need additional WITH constraint in your joins (last I checked NHibernate didn't support this). http://ayende.com/3495/nhibernate-worst-practice-sql#comment6http://ayende.com/3495/nhibernate-worst-practice-sql#comment6Thu, 07 Aug 2008 19:39:55 GMTIf you have to use native sql, wouldn't it be easier to create and map a stored procedure to an object instead of embedding native sql into your app? http://ayende.com/3495/nhibernate-worst-practice-sql#comment5http://ayende.com/3495/nhibernate-worst-practice-sql#comment5Thu, 07 Aug 2008 17:36:11 GMTSometimes it is the mappin and the db model making you go down that ugly route. http://ayende.com/3495/nhibernate-worst-practice-sql#comment4http://ayende.com/3495/nhibernate-worst-practice-sql#comment4Thu, 07 Aug 2008 07:43:33 GMTHQL and the Criteria API are very powerfull and they handle most scenarios, but I hit a problem recently. I wanted to join to a subquery (subquery that contained a projection and a group by). This did not appear to be possible, so I had to use a sql query directly, but this is the second time of 3 year of nhibernate usage I had to use native sql. http://forum.hibernate.org/viewtopic.php?t=989057&sid=918dd164816d8fba3158fd2389306660 http://ayende.com/3495/nhibernate-worst-practice-sql#comment3http://ayende.com/3495/nhibernate-worst-practice-sql#comment3Thu, 07 Aug 2008 06:24:00 GMTUsing stored procs or views when it's not necessary (like Ayende said - for queries that the programmer doesn't know how to write in HQL) has the same disadvantages (just that the SQL code is somewhere else) ... http://ayende.com/3495/nhibernate-worst-practice-sql#comment2http://ayende.com/3495/nhibernate-worst-practice-sql#comment2Thu, 07 Aug 2008 06:17:25 GMTno kidding huh? there are 1001 ways to avoid going that route. stored procs/udfs/views add a nice layer of indirection between he server and database internals http://ayende.com/3495/nhibernate-worst-practice-sql#comment1http://ayende.com/3495/nhibernate-worst-practice-sql#comment1Thu, 07 Aug 2008 01:54:39 GMT