you are using an old version of NH or you have disabled the autoimport of keywords. You can inherit from the dialect and add the keyword TOP.
2010/1/30 graphicsxp <[email protected]> > Anyone ? I've very puzzled regarding the 'this_' part of the query > generated by NH... > > On 27 jan, 16:04, graphicsxp <[email protected]> wrote: > > Hi, > > > > I'm doing a subquery using a formula in one of my properties, which > > results in an exception : > > > > Incorrect syntax near the keyword 'TOP'. > > > > The diagram between my tables looks like this: > > > > http://buzzmyart.s3.amazonaws.com/diag.jpg > > > > Note that the SQL generated by NH is not correct because it adds a > > 'this_' before the keyword TOP, which leads to the error. If I take > > off the 'this_' part, the query works just fine. > > > > Here are the SQL generated by NH as well as the mapping file: > > > > SELECT > > this_.BuzzGalleryID as BuzzGall1_5_1_, > > this_.BuzzUserID as BuzzUserID5_1_, > > this_.Name as Name5_1_, > > this_.DateCreated as DateCrea4_5_1_, > > this_.DateModified as DateModi5_5_1_, > > this_.Scrapped as Scrapped5_1_, > > ( > > SELECT > > this_.TOP 1 bua.URLThumbnail > > --------------------------------------------------------------------- > > > > > ERROR IS HERE > > > > FROM dbo.BuzzGalleryArt ba > > INNER JOIN dbo.BuzzUserArt bua ON > > ba.BuzzUserArtID = bua.BuzzUserArtID > > INNER JOIN dbo.BuzzGallery bg ON > > ba.BuzzGalleryID = bg.BuzzGalleryID > > > > WHERE bua.Scrapped = 0 AND ba.BuzzGalleryID = > this_.BuzzGalleryID) > > as formula0_1_, > > buzzuser2_.BuzzUserID as BuzzUserID0_0_, > > buzzuser2_.FirstName as FirstName0_0_, > > buzzuser2_.LastName as LastName0_0_, > > buzzuser2_.FullName as FullName0_0_, > > buzzuser2_.Email as Email0_0_, > > buzzuser2_.UserName as UserName0_0_, > > buzzuser2_.Password as Password0_0_, > > buzzuser2_.PasswordSalt as Password8_0_0_, > > buzzuser2_.PasswordFormat as Password9_0_0_, > > buzzuser2_.FailedPasswordAttemptCount as FailedP10_0_0_, > > buzzuser2_.FailedPasswordAttemptWindowStart as FailedP11_0_0_, > > buzzuser2_.LastPasswordChangedDate as LastPas12_0_0_, > > buzzuser2_.LastActivityDate as LastAct13_0_0_, > > buzzuser2_.isApproved as isApproved0_0_, > > buzzuser2_.isLockedOut as isLocke15_0_0_, > > buzzuser2_.LastLockOutDate as LastLoc16_0_0_, > > buzzuser2_.LastLoginDate as LastLog17_0_0_, > > buzzuser2_.DateCreated as DateCre18_0_0_, > > buzzuser2_.DateModified as DateMod19_0_0_, > > buzzuser2_.Scrapped as Scrapped0_0_, > > buzzuser2_.URLPhoto as URLPhoto0_0_, > > buzzuser2_.PersonalWebsite as Persona22_0_0_, > > buzzuser2_.Biography as Biography0_0_, > > buzzuser2_.Location as Location0_0_, > > buzzuser2_.Language as Language0_0_ > > FROM BuzzGallery this_ inner join BuzzUser buzzuser2_ on > > this_.BuzzUserID=buzzuser2_.BuzzUserID > > WHERE ( > > this_.Scrapped=0) > > > > *********************************** > > > > MAPPING: > > > > ************************************ > > > > <?xml version="1.0" encoding="utf-8" ?> > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> > > <class name="BuzzArt.BuzzGallery, BuzzArt" table="BuzzGallery" > > where="Scrapped=0"> > > > > <cache usage="read-write" region="longCache" /> > > > > <id name="Id" column="BuzzGalleryID" type="guid"> > > <generator class="guid.comb"/> > > </id> > > > > <many-to-one name="Owner" class="BuzzArt.BuzzUser, BuzzArt" > > column="BuzzUserID" not-null="true" /> > > > > <property name="Name" type="String" length="100" /> > > <property name="DateCreated" type="Date" insert="false" > > update="false" /> > > <property name="DateModified" type="Date" insert="false" /> > > <property name="Scrapped" type="Boolean" insert="false" /> > > > > <property name="UrlThumbnail" formula="(SELECT TOP 1 > > bua.URLThumbnail > > FROM dbo.BuzzGalleryArt ba > > INNER JOIN dbo.BuzzUserArt bua ON > > ba.BuzzUserArtID = bua.BuzzUserArtID > > INNER JOIN dbo.BuzzGallery bg ON > > ba.BuzzGalleryID = bg.BuzzGalleryID > > WHERE bua.Scrapped = 0 AND ba.BuzzGalleryID = > > BuzzGalleryID)"></property> > > > > <set name="Arts" table="BuzzGalleryArt" lazy="true" cascade="all" > > > > <key column="BuzzGalleryID" /> > > <many-to-many class="BuzzArt.BuzzUserArt, BuzzArt" > > column="BuzzUserArtID" where="Scrapped=0"/> > > </set> > > > > </class> > > </hibernate-mapping> > > > > Any idea what might cause the 'this_' to be added to the TOP keyword ? > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- Fabio Maulo -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
