I am developing a system with .NET 2.0 framework and nHibernate and MSSQL 2000. The biggest problem i am facing is in generating complex queries with SQL functions and involving three four tables... so far i am unable to find a tutorial or example describing how to write these queries which have more than couple of tables and some SQL specific terms..

A simple example of such queries is as below
string query = "SELECT DISTINCT category.*" +
" FROM tblItemBO item, tblItemDetailBO itemdetail, tblCountryBO country, tblCategoryBO category" +
" WHERE item.itemDetail_itemDetailBOId = itemdetail.itemDetailBOId" +
" AND itemdetail.itemCategory_categoryId = category.categoryId" +
" AND itemdetail.itemCountry_countryId = country.countryId" +
" AND category.isActive = 1" +
" AND item.auctionClosed = 0" +
" AND country.countryName = '" + myBO.getCountryName() + "'";

Now queries like these keep giving error like "error in FindBySQL"
and .NET throws the following error in browser.


I have searched for quite a few on google but even after 2 days i m clueless.. i m thinking of switching this querty into ICriteria style but i m not finding any good tutorial to help me know how to use multiple tables in the nHibernate

Any help and guidance will be much appreciated

A good source for Hibernate queries is the book "Hibernate in Action". It is based on the Java version but most of the concepts described there can be ported to the .NET world. There is a second edition named "Java persistence with Hibernate" out since a few weeks. I would prefer the first edition because the second edition is based on Hibernate 3.0.

Regards
Klaus