Hi,
I'm trying to do a preload a many-to-one, and a one-to-many collection property using join fetch efficiently and found out the underlying SQL execution plan wasn't efficient, and best is to provide in the SQL statement to do join on a given index. How do I do that? Does NHibernate has an API to provide hints for join? Here's my hql code:
Code:
string hql = String.Format(@"from Sample s join fetch s.msrmtData md join fetch md.msrmt m where s.facility.id={0} and s.collectionDateTime between '{1} 0:00' and '{2} 23:59:59' ", facilityID, beginDate.ToShortDateString(), maxEndDate.ToShortDateString());
IList result = hbSession.Find(hql);
I've attempted to use native SQL to provide the hint but I got NHibernate exception w/o usefull error messages. Is it supported?
Code:
string sql = String.Format(@"select distinct {{s.*}} from tSamples s
inner join tDWFacSiteMsrmtData md (index (idx2_tdwfacsitemsrmtdata)) on s.sSampleID=md.WTXSampleID
inner join tDWFacSiteMsrmts m on md.DWMsrmtID=m.DWMsrmtID
where s.tmpDWFacSiteID={0} and (s.sSampleCollectionDateTime between '{1} 0:00' and '{2} 23:59:59')", facilityID, minBeginDate.ToShortDateString(), maxEndDate.ToShortDateString());
IQuery q = hbSession.CreateSQLQuery(sql, new string[]{"s","md","m"}, new Type[]{typeof(Sample), typeof(DWFacSiteMsrmtData), typeof(DWFacSiteMsrmt)});
IList samples = q.List();
Exception thrown:
Quote:
005-10-17 16:20:58,455 ERROR [9e-0690-4041-b7f9-682acf01a9dc] - System.IndexOutOfRangeException: MsrmtDat1_1_
at System.Data.Common.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, IList hydratedObjects, Object optionalObject, Object optionalId, Key[] keys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.SqlLoader.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.FindBySQL(String sqlQuery, String[] aliases, Type[] classes, QueryParameters queryParameters, ICollection querySpaces)
at NHibernate.Impl.SqlQueryImpl.List()
My mappings:Quote:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="WaterTrax.WTX.WTXObjectModel.Measurements.Sample, WTXObjectModel" table="tSamples" proxy="WaterTrax.WTX.WTXObjectModel.Measurements.Sample, WTXObjectModel">
<id name="id" column="sSampleID" unsaved-value="0">
<generator class="native" />
</id>
<property name="description" column="sDescr" />
<property name="identifier" column="sSampleIdentifier" />
<property name="collectionDateTime" column="sSampleCollectionDateTime" />
<bag name="msrmtData" inverse="true" lazy="true">
<key column="WTXSampleID"/>
<one-to-many class="WaterTrax.WTX.WTXObjectModel.Measurements.DWFacSiteMsrmtData, WTXObjectModel"/>
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="WaterTrax.WTX.WTXObjectModel.Measurements.DWFacSiteMsrmtData, WTXObjectModel" table="tDWFacSiteMsrmtData" proxy="WaterTrax.WTX.WTXObjectModel.Measurements.DWFacSiteMsrmtData, WTXObjectModel">
<id name="id" column="MsrmtDataID" unsaved-value="0">
<generator class="native" />
</id>
<many-to-one name="msrmt"
class="WaterTrax.WTX.WTXObjectModel.Measurements.DWFacSiteMsrmt, WTXObjectModel"
column="DWMsrmtID"/>
<many-to-one name="sample"
class="WaterTrax.WTX.WTXObjectModel.Measurements.Sample, WTXObjectModel"
column="WTXSampleID"/>
<property name="analyticalResultVal" column="AnalyticalResultVal" />
<property name="presence" column="Presence" />
<property name="timeProvided" column="TimeProvidedFlag" />
<property name="sampleCollectionDateTime" column="SampleCollectionDateTime"/>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="WaterTrax.WTX.WTXObjectModel.Measurements.DWFacSiteMsrmt, WTXObjectModel" table="tDWFacSiteMsrmts" proxy="WaterTrax.WTX.WTXObjectModel.Measurements.DWFacSiteMsrmt, WTXObjectModel">
<id name="id" column="DWMsrmtID">
<generator class="native" />
</id>
<many-to-one name="facility"
class="WaterTrax.WTX.WTXObjectModel.Facilities.Facility, WTXObjectModel"
column="FacilityID"/>
<many-to-one name="dwFacSite"
class="WaterTrax.WTX.WTXObjectModel.WaterSources.DWFacSite, WTXObjectModel"
column="DWFacSiteID"/>
<many-to-one name="readingType"
class="WaterTrax.WTX.WTXObjectModel.SamplingEntities.ReadingType, WTXObjectModel"
column="ReadingTypeID"/>
<many-to-one name="reading"
class="WaterTrax.WTX.WTXObjectModel.SamplingEntities.Reading, WTXObjectModel"
column="ReadingID"/>
<many-to-one name="unit"
class="WaterTrax.WTX.WTXObjectModel.Measurements.Unit, WTXObjectModel"
column="UnitsID"/>
<property name="descr" column="Descr"/>
</class>
</hibernate-mapping>
Login
FAQ
Search