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 view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/jlShrXL0FN4J.
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.
