Hi,
I have two mappings, one with 2 named queries. Everything maps fine to the objects when I execute 2 separate repository calls - one for each object. The mappings for this are below:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Company.Application.Models" assembly="Company.Application.Models">
<class name="ProductVersion" lazy="false">
<composite-id>
<key-property name="PracticeName">
<column name="practice_name" not-null="true" />
</key-property>
<key-property name="Address">
<column name="address_line_1" not-null="true" />
</key-property>
<key-property name="City">
<column name="city" not-null="true" />
</key-property>
<key-property name="State">
<column name="state" not-null="true" />
</key-property>
<key-property name="Zip">
<column name="zip" not-null="true" />
</key-property>
</composite-id>
<property name="Version" column="version" />
<bag name="ProductsLicensesDetail" inverse="true">
<key>
<column name="PracticeName" />
<column name="Address" />
<column name="City" />
<column name="State" />
<column name="Zip" />
</key>
<one-to-many class="ProductLicenseDetail" />
</bag>
</class>
<sql-query name="ProductVersion">
<return alias="t1" class="ProductVersion"/>
select distinct pr.practice_name as {t1.PracticeName}, pr.address_line_1 as {t1.Address}, pr.city as {t1.City}, pr.state as {t1.State}, pr.zip as {t1.Zip},
(select top 1 version from version order by create_timestamp desc) as {t1.Version}
from license_detail ld
inner join practice pr on ld.limit_value = pr.practice_id
where ld.limit_value = '0001'
</sql-query>
<sql-query name="ProductLicenseDetailQuery">
<return alias="t1" class="ProductLicenseDetail"/>
select t1.practice_name as {t1.PracticeName}, t1.address_line_1 as {t1.Address}, t1.city as {t1.City}, t1.state as {t1.State}, t1.zip as {t1.Zip}, t1.product_id as {t1.ProductCode},
count(case when ld.limit_id like '%ENT' then ld.limit_id end) as {t1.TotalEnterpriseLicenses},
count(case when ld.limit_id like '%ENT' and rtrim(ld.limit_value) != '' then ld.limit_id end) as {t1.EnterpriseLicensesUsed},
count(case when ld.limit_id like '%PRAC' then ld.limit_id end) as {t1.TotalPracticeLicenses},
count(case when ld.limit_id like '%PRAC' and rtrim(ld.limit_value) != '' then ld.limit_id end) as {t1.PracticeLicensesUsed},
count(case when ld.limit_id like '%PROV' then ld.limit_id end) as {t1.TotalProviderLicenses},
count(case when ld.limit_id like '%PROV' and rtrim(ld.limit_value) != '' then ld.limit_id end) as {t1.ProviderLicensesUsed},
count(case when ld.limit_id like '%USER' then ld.limit_id end) as {t1.TotalUserLicenses},
count(case when ld.limit_id like '%USER' and rtrim(ld.limit_value) != '' then ld.limit_id end) as {t1.UserLicensesUsed},
t1.license_key as {t1.LicenseKey}
from
(
select lm.product_id, lm.license_key, pr.practice_name, pr.address_line_1, pr.city, pr.state, pr.zip
from license_mstr lm
left join license_detail ld on lm.product_id = ld.product_id
left join practice pr on ld.limit_value = pr.practice_id
where ld.limit_value = '0001'
) t1
inner join license_detail ld on t1.product_id = ld.product_id
group by t1.practice_name, t1.address_line_1, t1.city, t1.state, t1.zip, t1.product_id, t1.license_key
</sql-query>
</hibernate-mapping>
and
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Company.Application.Models" assembly="Company.Application.Models">
<class name="ProductLicenseDetail" lazy="false">
<composite-id>
<key-property name="PracticeName">
<column name="practice_name" not-null="true" />
</key-property>
<key-property name="Address">
<column name="address_line_1" not-null="true" />
</key-property>
<key-property name="City">
<column name="city" not-null="true" />
</key-property>
<key-property name="State">
<column name="state" not-null="true" />
</key-property>
<key-property name="Zip">
<column name="zip" not-null="true" />
</key-property>
</composite-id>
<property name="ProductCode" column="product_id" />
<property name="TotalEnterpriseLicenses" column="total_enterprise" />
<property name="EnterpriseLicensesUsed" column="total_enterprise_used" />
<property name="TotalPracticeLicenses" column="total_practice" />
<property name="PracticeLicensesUsed" column="total_practice_used" />
<property name="TotalProviderLicenses" column="total_provider" />
<property name="ProviderLicensesUsed" column="total_provider_used" />
<property name="TotalUserLicenses" column="total_user" />
<property name="UserLicensesUsed" column="total_user_used" />
<property name="LicenseKey" column="license_key" />
</class>
</hibernate-mapping>
Now, when I try to use a loader for the "ProductsLicensesDetail" collection of "ProductVersion", I get the "error in named query" exception. A snippit of the mapping that doesn't work is below:
<bag name="ProductsLicensesDetail" inverse="true">
<key>
<column name="PracticeName" />
<column name="Address" />
<column name="City" />
<column name="State" />
<column name="Zip" />
</key>
<one-to-many class="ProductLicenseDetail" />
<loader query-ref="ProductLicenseDetailQuery"/>
</bag>
</class>
<return alias="t1" class="ProductVersion"/>
...
...
</sql-query>
<sql-query name="ProductLicenseDetailQuery">
<load-collection alias="t1" role="ProductVersion.ProductsLicensesDetail"/>
select t1.practice_name as {t1.PracticeName}, t1.address_line_1 as {t1.Address}, t1.city as {t1.City}, t1.state as {t1.State}, t1.zip as {t1.Zip}, t1.product_id as {t1.ProductCode},
count(case when ld.limit_id like '%ENT' then ld.limit_id end) as {t1.TotalEnterpriseLicenses},
....
....
</sql-query>
</hibernate-mapping>
Can someone please tell me what I'm doing wrong? I'm using the same exact query in the "ProductLicenseDetailQuery" named query. Why does it work when calling .List<>() in the code, but it doesn't work when using the loader?