Hi,

I am trying to use an ICriterion to query my database combining Nhibernate ql with my own sql like this :

HibernateManager.GetSession()
.CreateCriteria(typeof(NettobjektList))
.CreateAlias("Objekt", "objk")
.Add(Expression.Sql("contains(objk.Katalogtekst2, 'test')"))
.List<Objekt>();

The problem is that "objk" in the generated sql turns out to become "objk1_" (see sql below) , so how on earth am I to know which alias to use then in my own sql. Of course after having run this code some times it seems that I might just add the "1_" to the alias in my sql, but that sure feels like a bad general solution...


Hibernate version:1.2 beta 3

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:MSSQL 2005

The generated SQL (show_sql=true):
SELECT this_.Nettobjektid as Nettobje1_36_1_, this_.Start as Start36_1_, this_.Slutt as Slutt36_1_, this_.Kontraktnr as Kontraktnr36_1_, this_.Lopenr as Lopenr36_1_, this_.Katalogtekst2 as Katalogt6_36_1_, this_.Vurderingspris2 as Vurderin7_36_1_, this_.Kategori as Kategori36_1_, this_.Selger as Selger36_1_, this_.Kjoper as Kjoper36_1_, this_.Objektid as Objektid36_1_, objk1_.Objektid as Objektid48_0_, objk1_.HBVersion as HBVersion48_0_, objk1_.Kontraktnr as Kontraktnr48_0_, objk1_.Lopenr as Lopenr48_0_, objk1_.Objekt1 as Objekt5_48_0_, objk1_.Objekt3 as Objekt6_48_0_, objk1_.Kategori as Kategori48_0_, objk1_.Kunstner as Kunstner48_0_, objk1_.Mvasats as Mvasats48_0_, objk1_.Kunstneravgift as Kunstne10_48_0_, objk1_.Salar as Salar48_0_, objk1_.Vurderingspris1 as Vurderi12_48_0_, objk1_.Vurderingspris2 as Vurderi13_48_0_, objk1_.Fotokostnad as Fotokos14_48_0_, objk1_.Transport as Transport48_0_, objk1_.Forsikring as Forsikring48_0_, objk1_.Etikett as Etikett48_0_, objk1_.Minstepris as Minstepris48_0_, objk1_.Tiltak as Tiltak48_0_, objk1_.Merknad as Merknad48_0_, objk1_.Sign as Sign48_0_, objk1_.Salgspris as Salgspris48_0_, objk1_.Refusert as Refusert48_0_, objk1_.Auksjon as Auksjon48_0_, objk1_.Auksjon0 as Auksjon25_48_0_, objk1_.Auksjon1 as Auksjon26_48_0_, objk1_.Auksjon2 as Auksjon27_48_0_, objk1_.Auksjon3 as Auksjon28_48_0_, objk1_.Katalogtekst1 as Katalog29_48_0_, objk1_.Katalogtekst2 as Katalog30_48_0_, objk1_.Katalognr as Katalognr48_0_, objk1_.Katbnr as Katbnr48_0_, objk1_.Innlevav as Innlevav48_0_, objk1_.Mottaker as Mottaker48_0_, objk1_.Registrert_dato as Registrert35_48_0_, objk1_.Registrert_av as Registrert36_48_0_, objk1_.Sist_endret_tid as Sist37_48_0_, objk1_.Sist_endret_av as Sist38_48_0_, objk1_.Tilslagnr as Tilslagnr48_0_, objk1_.Fakturert as Fakturert48_0_, objk1_.Tilslagtekst as Tilslag41_48_0_, objk1_.Fototatt as Fototatt48_0_, objk1_.Bet_dato as Bet43_48_0_, objk1_.Lev_dato as Lev44_48_0_, objk1_.Tekst_kost as Tekst45_48_0_, objk1_.Oppgjor as Oppgjor48_0_, objk1_.Oppbelop as Oppbelop48_0_, objk1_.Bilde as Bilde48_0_, objk1_.Godkjentforinternett as Godkjen49_48_0_, objk1_.Mfgs as Mfgs48_0_, objk1_.Sendtresultatbrev as Sendtre51_48_0_, objk1_.Kontraktkost as Kontrak52_48_0_, objk1_.Frakttilkjoper as Fraktti53_48_0_, objk1_.Salarbelop as Salarbelop48_0_, objk1_.Eksportert as Eksportert48_0_, objk1_.Oppgjortransnr as Oppgjor56_48_0_, objk1_.Oppgjorbetalt as Oppgjor57_48_0_, objk1_.Oppgjortranskode as Oppgjor58_48_0_, objk1_.Oppgjorgodkjendt as Oppgjor59_48_0_, objk1_.Oppgjorutbetalt as Oppgjor60_48_0_, objk1_.Oppgjorbilagsnr as Oppgjor61_48_0_, objk1_.Salgbilagsnr as Salgbil62_48_0_, objk1_.Overfortinternett as Overfor63_48_0_, objk1_.Fakturadato as Faktura64_48_0_, objk1_.Objekttype as Objekttype48_0_, objk1_.Hoyde as Hoyde48_0_, objk1_.Lengde as Lengde48_0_, objk1_.Dybde as Dybde48_0_, objk1_.Diameter as Diameter48_0_, objk1_.Vekt as Vekt48_0_, objk1_.Overfortlager as Overfor71_48_0_, objk1_.Katalogteksteng as Katalog72_48_0_, objk1_.Lokasjon as Lokasjon48_0_, objk1_.Antall as Antall48_0_, objk1_.Antalletikett as Antalle75_48_0_, objk1_.Kansendes as Kansendes48_0_, objk1_.Tekst_ok as Tekst77_48_0_, objk1_.Fototrykk as Fototrykk48_0_, objk1_.Avdelingsid as Avdelin79_48_0_, objk1_.Rettelse as Rettelse48_0_ FROM [dbo].[NettobjektList] this_
inner join [dbo].[Objekt] objk1_ on this_.Objektid=objk1_.Objektid WHERE this_.Start <= @p0 and this_.Slutt >= @p1 and contains(objk.Katalogtekst2, 'hans'); @p0 = '17.01.2007 16:53:15', @p1 = '17.01.2007 16:53:15'



Debug level Hibernate log excerpt:

Well, I actually found out myself (by reading the sourcecode). Just needed to replace "objk" with "{alias}" like this :

HibernateManager.GetSession()
.CreateCriteria(typeof(NettobjektList))
.CreateCriteria("Objekt", "objk")
.Add(Expression.Sql("contains({alias}.Katalogtekst2, 'test')"))
.List<Objekt>();