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 nhus...@googlegroups.com. To unsubscribe from this group, send email to nhusers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
