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.
