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]. > 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]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
