Thanks Maciej.
I fixed my problem I found the solution right here:
http://www.methodicmadness.com/2009/01/nhibernate-what-is-heck-clazz.html
It had to do with the inheritance and the plain sql that i was feeding
the query. The working method is found below for anyone else running
into the same issue:
public IEnumerable<Organization>
GetAllAncestorsOfOrganization(long organizationId)
{
var sql = "with
Hierachy(Id,Name,ParentId,OrgTypeId,SecurityKey,IsInactive,Level,
clazz_)"+
" as (select Id,"+
" Name,"+
" ParentId,"+
" OrgTypeId,"+
" SecurityKey,"+
" IsInactive,"+
" 0 as Level,"+
" case "+
" when
organizati0_1_.OrganizationId is not null
then 1"+
" when
organizati0_2_.OrganizationId is not null
then 2"+
" when
organizati0_3_.OrganizationId is not null
then 3"+
" when
organizati0_4_.OrganizationId is not null
then 4"+
" when organizati0_.Id is not
null then 0"+
" end "+
" from core_Organization organizati0_"+
" left outer join
[core_SchoolDetail]
organizati0_1_"+
" on
organizati0_.Id =
organizati0_1_.OrganizationId"+
" left outer join
[core_DistrictDetail]
organizati0_2_"+
" on
organizati0_.Id =
organizati0_2_.OrganizationId"+
" left outer join
[core_RegionDetail]
organizati0_3_"+
" on
organizati0_.Id =
organizati0_3_.OrganizationId"+
" left outer join
[core_NonAvidOrgDetail]
organizati0_4_"+
" on
organizati0_.Id =
organizati0_4_.OrganizationId"+
" where organizati0_.Id = :id" +
" union all"+
" select e.Id,"+
" e.Name,"+
" e.ParentId,"+
" e.OrgTypeId,"+
" e.SecurityKey,"+
" e.IsInactive,"+
" eh.Level + 1,"+
" eh.clazz_"+
" from core_Organization e"+
" inner join Hierachy eh"+
" on e.Id = eh.ParentId)"+
" select Id,"+
" Name,"+
" ParentId,"+
" OrgTypeId,"+
" SecurityKey,"+
" IsInactive,"+
" clazz_"+
" from Hierachy"+
" where Level > 0";
var list = UnitOfWork.CurrentSession.CreateSQLQuery(sql)
.AddEntity(typeof(Organization))
.SetInt64("id", organizationId)
.List<Organization>();
return list;
}
On Jun 9, 2:51 pm, Maciej Szczepanski <[email protected]> wrote:
> It looks like your query does not return Organization entity, as some
> properties are missing.
> See:http://nhforge.org/doc/nh/en/index.html#querysql-creating
>
> You could also try using AddScalar instead of AddEntitiy, and then load
> those Organizations based on the keys returned by your query. (This solution
> would require second query with "in" in the where clause)
>
>
>
>
>
> On Wed, Jun 9, 2010 at 11:30 PM, beto <[email protected]> wrote:
> > Does anyone have any idea on why I am not able to run the query? I
> > have a Linq version of the query but I would much rather have the
> > native sql query run since the linq version is slow.
>
> > Any help would be appreciated. Thank You
>
> > On Apr 26, 2:00 pm, beto <[email protected]> wrote:
> > > Hi Everyone
>
> > > When I try to run the following query I receive a
> > > System.IndexOutOfRangeException. But if I run just the plain SQL, in
> > > SQL Management Studio the query runs with out any problems. Below you
> > > will find the method, my mapping (i have 4 subclasses)and the stack
> > > trace.
>
> > > public IEnumerable<Organization>
> > > GetAllAncestorsOfOrganization(long organizationId)
> > > {
> > > var sql = "with Hierachy(Id, Name, ParentId, Level)" +
> > > " as" +
> > > " (" +
> > > " select Id, Name, ParentId, 0 as Level" +
> > > " from core_Organization e" +
> > > " where e.Id = :id" +
> > > " union all" +
> > > " select e.Id, e.Name, e.ParentId,
> > > eh.Level + 1" +
> > > " from core_Organization e" +
> > > " inner join Hierachy eh" +
> > > " on e.Id = eh.ParentId" +
> > > " )" +
> > > " select Id, Name, ParentId" +
> > > " from Hierachy" +
> > > " where Level > 0";
> > > var list = UnitOfWork.CurrentSession.CreateSQLQuery(sql)
> > > .AddEntity(typeof (Organization))
> > > .SetInt64("id", organizationId)
> > > .List<Organization>();
> > > return list;
> > > }
>
> > > <class name="Organization" table="[core_Organization]" lazy="true">
>
> > > <id name="Id" access="property" column="Id" type="Int64" unsaved-
> > > value="0">
> > > <generator class="native" />
> > > </id>
>
> > > <property name="Name" access="property" type="String" not-
> > > null="true" />
>
> > > <many-to-one name="Parent" class="Organization" column="ParentId"
> > > not-null="false" />
> > > <many-to-one name="OrganizationType" class="OrganizationType"
> > > column="OrgTypeId" not-null="true" />
>
> > > <property name="SecurityKey" access="property" type="System.Guid">
> > > <column name="SecurityKey" not-null="true" />
> > > </property>
>
> > > <set name="Children" cascade="save-update">
> > > <key column="ParentId" foreign-
> > > key="fk_Organization_Parent_ParentOrganization"/>
> > > <one-to-many class="Organization"/>
> > > </set>
>
> > > <set name="Notes" cascade="save-update">
> > > <key column="OrganizationId" foreign-
> > > key="fk_Organization_OrganizationId_OrgnizationNote"/>
> > > <one-to-many class="OrganizationNote"/>
> > > </set>
>
> > > <property name="IsInactive"/>
>
> > > NHibernate.ADOException was unhandled
> > > Message="could not execute query\r\n[ with Hierachy(Id, Name,
> > > ParentId, Level) as ( select Id, Name, ParentId, 0 as Level from
> > > core_Organization e where e.Id = @p0 union all select e.Id,
> > > e.Name, e.ParentId, eh.Level + 1 from core_Organization e inner
> > > join Hierachy eh on e.Id = eh.ParentId ) select Id, Name,
> > > ParentId from Hierachy where Level > 0 ]\r\n Name:id - Value:1\r
> > > \n[SQL: with Hierachy(Id, Name, ParentId, Level) as ( select Id,
> > > Name, ParentId, 0 as Level from core_Organization e where e.Id =
> > > @p0 union all select e.Id, e.Name, e.ParentId, eh.Level + 1 from
> > > core_Organization e inner join Hierachy eh on e.Id =
> > > eh.ParentId ) select Id, Name, ParentId from Hierachy where Level >
> > > 0]"
> > > Source="NHibernate"
> > > SqlString="with Hierachy(Id, Name, ParentId, Level) as ( select
> > > Id, Name, ParentId, 0 as Level from core_Organization e where e.Id
> > > = @p0 union all select e.Id, e.Name, e.ParentId, eh.Level + 1
> > > from core_Organization e inner join Hierachy eh on e.Id =
> > > eh.ParentId ) select Id, Name, ParentId from Hierachy where Level > 0"
> > > StackTrace:
> > > at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
> > > QueryParameters queryParameters)
> > > at
> > > NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
> > > session, QueryParameters queryParameters)
> > > at NHibernate.Loader.Loader.List(ISessionImplementor session,
> > > QueryParameters queryParameters, ISet`1 querySpaces, IType[]
> > > resultTypes)
> > > at
> > > NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor
> > > session, QueryParameters queryParameters)
> > > at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery
> > > customQuery, QueryParameters queryParameters, IList results)
> > > at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification
> > > spec, QueryParameters queryParameters, IList results)
> > > at NHibernate.Impl.SessionImpl.List[T]
> > > (NativeSQLQuerySpecification spec, QueryParameters queryParameters)
> > > at NHibernate.Impl.SqlQueryImpl.List[T]()
> > > at
>
> > DomainModel.Repositories.OrganizationRepository.GetAllAncestorsOfOrganizati
> > on(Int64
> > > organizationId) in C:\Users\hfranco\Documents\Visual Studio
> > > 2008\Projects\MyAVID\src\MyAVIDMVC2\Core\DomainModel\Repositories
> > > \OrganizationRepository.cs:line 87
> > > at DBHiearchyBuild.Program.Main(String[] args) in C:\Users
> > > \hfranco\Documents\Visual Studio 2008\Projects\MyAVID\src
> > > \MyAVIDMVC2\Core\DBHiearchyBuild\Program.cs:line 46
> > > at System.AppDomain._nExecuteAssembly(Assembly assembly,
> > > String[] args)
> > > at
> > > Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
> > > at System.Threading.ExecutionContext.Run(ExecutionContext
> > > executionContext, ContextCallback callback, Object state)
> > > at System.Threading.ThreadHelper.ThreadStart()
> > > InnerException: System.IndexOutOfRangeException
> > > Message="clazz_"
> > > Source="System.Data"
> > > StackTrace:
> > > at
> > > System.Data.ProviderBase.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 name, ISessionImplementor session, Object owner)
> > > at NHibernate.Loader.Loader.GetInstanceClass(IDataReader
> > > rs, Int32 i, ILoadable persister, Object id, ISessionImplementor
> > > session)
> > > at
> > > NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i,
> > > ILoadable persister, EntityKey key, LockMode lockMode, String
> > > rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList
> > > hydratedObjects, ISessionImplementor session)
> > > at NHibernate.Loader.Loader.GetRow(IDataReader rs,
> > > ILoadable[] persisters, EntityKey[] keys, Object optionalObject,
> > > EntityKey optionalObjectKey, LockMode[] lockModes, IList
> > > hydratedObjects, ISessionImplementor session)
> > > at
> > > NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet,
> > > ISessionImplementor session, QueryParameters queryParameters,
> > > LockMode[] lockModeArray, EntityKey optionalObjectKey, IList
> > > hydratedObjects, EntityKey[] keys, Boolean returnProxies)
> > > at NHibernate.Loader.Loader.DoQuery(ISessionImplementor
> > > session, QueryParameters queryParameters, Boolean returnProxies)
> > > at
>
> > NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImp
> > lementor
> > > session, QueryParameters queryParameters, Boolean returnProxies)
> > > at NHibernate.Loader.Loader.DoList(ISessionImplementor
> > > session, QueryParameters queryParameters)
> > > InnerException:
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > > To post to this group, send email to [email protected].
> > > To unsubscribe from this group, send email to
> > [email protected]<nhusers%[email protected]
> > >
> > .
> > > For more options, visit this group athttp://
> > groups.google.com/group/nhusers?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected]<nhusers%[email protected]
> > >
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.
>
> --
> .
> Maciej Szczepański
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
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.
