Hey, Tom! Did you ever find out what was going on on your case?
I have a very similar situation, the application starts and it might produce a wrong SQL, we restart it and everything works as expected. Thanks! Gus. On Wednesday, September 19, 2012 6:40:45 AM UTC-3, Tom Ceuppens wrote: > > I'm having a problem where for some unknown reason the wrong SQL is being > generated by NHibernate. > > The following is my mapping file: > <?xml version="1.0" encoding="utf-8" ?> > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > assembly="BAM.PMT.Domain" > namespace="BAM.PMT.Domain.Core.Cos"> > > <class name="SiteServiceEntity" table="SiteServiceEntity" > schema="core_cos" dynamic-update="true" abstract="true" > discriminator-value="-1"> > <id name="Id" access="field.camelcase-underscore"> > <generator class="guid.comb" /> > </id> > > <discriminator column="Type" type="short" /> > > <component name="Name" access="field.camelcase-underscore"> > <property name="Nl" column="NameNl" /> > <property name="Fr" column="NameFr" /> > <property name="En" column="NameEn" /> > <property name="De" column="NameDe" /> > </component> > > <property name="Code" access="field.camelcase-underscore" /> > > <subclass name="BAM.PMT.Domain.Core.Cos.SiteServiceCategory" > dynamic-update="true" discriminator-value="0"> > <set name="SiteServiceGroups" table="SiteServiceGroup" schema="core_cos" > cascade="none" inverse="true" access="field.camelcase-underscore"> > <key column="SiteServiceCategoryId" /> > <one-to-many class="SiteServiceGroup" /> > </set> > <join table="SiteServiceCategory" schema="core_cos"> > <key column="Id"/> > > <many-to-one name="Site" class="BAM.PMT.Domain.Core.Sites.Site" > column="SiteId" cascade="none" access="field.camelcase-underscore" /> > </join> > </subclass> > > <subclass name="BAM.PMT.Domain.Core.Cos.SiteServiceGroup" > dynamic-update="true" discriminator-value="1"> > <set name="SiteServices" table="SiteService" schema="core_cos" > cascade="none" inverse="true" access="field.camelcase-underscore"> > <key column="SiteServiceGroupId" /> > <one-to-many class="SiteService" /> > </set> > <join table="SiteServiceGroup" schema="core_cos"> > <key column="Id"/> > > <many-to-one name="SiteServiceCategory" class="SiteServiceCategory" > column="SiteServiceCategoryId" cascade="none" > access="field.camelcase-underscore" /> > > </join> > </subclass> > > <subclass name="BAM.PMT.Domain.Core.Cos.SiteService" dynamic-update="true" > discriminator-value="2"> > <set name="Subcontractors" table="SiteServiceSubcontractor" > schema="core_cos" cascade="save-update" access="field.camelcase-underscore"> > <key column="SiteServiceId" /> > <many-to-many class="BAM.PMT.Domain.Core.Sites.Subcontractor" > column="SubcontractorId" /> > </set> > > <set name="SiteKpis" table="SiteKPI" schema="core_cos" cascade="none" > inverse="true" access="field.camelcase-underscore"> > <key column="SiteServiceId" /> > <one-to-many class="SiteKpi" /> > </set> > > <set name="SiteCauses" table="SiteCause" schema="helpdesk" cascade="none" > inverse="true" access="field.camelcase-underscore"> > <key column="SiteServiceId" /> > <one-to-many class="BAM.PMT.Domain.Helpdesk.SiteCause" /> > </set> > <join table="SiteService" schema="core_cos"> > <key column="Id"/> > > <many-to-one name="SiteServiceGroup" class="SiteServiceGroup" > column="SiteServiceGroupId" cascade="none" > access="field.camelcase-underscore" /> > <many-to-one name="Service" class="Service" column="ServiceId" > cascade="none" access="field.camelcase-underscore" /> > <many-to-one name="SiteResponsible" > class="BAM.PMT.Domain.Core.Security.User" column="SiteResponsibleId" > cascade="none" access="field.camelcase-underscore" /> > <many-to-one name="BamResponsible" > class="BAM.PMT.Domain.Core.Security.User" column="BamResponsibleId" > cascade="none" access="field.camelcase-underscore" /> > <many-to-one name="SubcontractorResponsible" > class="BAM.PMT.Domain.Core.Security.User" > column="SubContractorResponsibleId" cascade="none" > access="field.camelcase-underscore" /> > > </join> > </subclass> > </class> > > </hibernate-mapping> > > When I query the full tree structure using the following: > > var query = QueryOver.Of<SiteServiceCategory>(); > query > .OrderBy(s => s.Code).Asc() > .ThenBy(s => s.Id).Asc() > .Left.JoinQueryOver(sc => sc.SiteServiceGroups) > .OrderBy(sg => sg.Code).Asc() > .ThenBy(sg => sg.Id).Asc() > .Left.JoinQueryOver(sg => sg.SiteServices) > .OrderBy(s => s.Code).Asc() > .ThenBy(s => s.Id).Asc() > .Left.JoinQueryOver(s => s.Service); > > query > .Where(sc => sc.Site.Id == siteId) > .TransformUsing(Transformers.DistinctRootEntity); > > > > return query; > > The following SQL is generated: > > SELECT this_.id AS Id105_3_, > this_.namenl AS NameNl105_3_, > this_.namefr AS NameFr105_3_, > this_.nameen AS NameEn105_3_, > this_.namede AS NameDe105_3_, > this_.code AS Code105_3_, > this_1_.siteid AS SiteId106_3_, > siteservic1_1_.siteservicecategoryid AS SiteServ2_5_, > siteservic1_.id AS Id5_, > siteservic1_.id AS Id105_0_, > siteservic1_.namenl AS NameNl105_0_, > siteservic1_.namefr AS NameFr105_0_, > siteservic1_.nameen AS NameEn105_0_, > siteservic1_.namede AS NameDe105_0_, > siteservic1_.code AS Code105_0_, > siteservic1_1_.siteservicecategoryid AS SiteServ2_107_0_, > siteservic2_1_.siteservicegroupid AS SiteServ2_6_, > siteservic2_.id AS Id6_, > siteservic2_.id AS Id105_1_, > siteservic2_.namenl AS NameNl105_1_, > siteservic2_.namefr AS NameFr105_1_, > siteservic2_.nameen AS NameEn105_1_, > siteservic2_.namede AS NameDe105_1_, > siteservic2_.code AS Code105_1_, > siteservic2_1_.siteservicegroupid AS SiteServ2_109_1_, > siteservic2_1_.serviceid AS ServiceId109_1_, > siteservic2_1_.siteresponsibleid AS SiteResp4_109_1_, > siteservic2_1_.bamresponsibleid AS BamRespo5_109_1_, > siteservic2_1_.subcontractorresponsibleid AS SubContr6_109_1_, > service3_.id AS Id16_2_, > service3_.code AS Code16_2_, > service3_.namenl AS NameNl16_2_, > service3_.namefr AS NameFr16_2_, > service3_.nameen AS NameEn16_2_, > service3_.namede AS NameDe16_2_, > service3_.isactive AS IsActive16_2_, > service3_.servicegroupid AS ServiceG8_16_2_ > FROM core_cos.siteserviceentity this_ > INNER JOIN core_cos.siteservicecategory this_1_ > ON this_.id = this_1_.id > LEFT OUTER JOIN core_cos.siteserviceentity siteservic1_ > ON this_.id = siteservic1_.siteservicecategoryid > LEFT OUTER JOIN core_cos.siteservicegroup siteservic1_1_ > ON siteservic1_.id = siteservic1_1_.id > LEFT OUTER JOIN core_cos.siteserviceentity siteservic2_ > ON siteservic1_.id = siteservic2_.siteservicegroupid > LEFT OUTER JOIN core_cos.siteservice siteservic2_1_ > ON siteservic2_.id = siteservic2_1_.id > LEFT OUTER JOIN core_cos.service service3_ > ON siteservic2_1_.serviceid = service3_.id > WHERE this_.type = 0 > AND this_1_.siteid = @p0 > ORDER BY this_.code ASC, > this_.id ASC, > siteservic1_.code ASC, > siteservic1_.id ASC, > siteservic2_.code ASC, > siteservic2_.id ASC > > The problem is marked in red, those columns don't belong to the > SiteServiceEntity table, but to the SiteServiceCategory ( > siteservicecategoryid > ) or SiteServiceGrouptable (siteservicegroupid) tables. > > Anybody has any idea what is going on, extra info can be provided if > required. > > Thanks in advance! > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
