What is wrong with this query? Does NHIbernate not support sub-selects in the select list? I could not find any documentation on this subject. I am using SQL Server, witch does support this type of query nativly.

Thanks in advance,
Mardo

----------------------------------------
Query
-----------------------------------------
select organization.Name,
organization.Id,
(select count(orgCount.Id) from Mimi.DM.Organization as orgCount where orgCount.Parent.Id=organization.Id)
FROM Mimi.DM.Organization as organization where organization.Parent.Id=3

--------------------------------------------
Error
--------------------------------------------

Server Error in '/Mimi' Application.
--------------------------------------------------------------------------------

aggregate function expected before ( in SELECT [select organization.Name, organization.Id, (select count(orgCount.Id) from Mimi.DM.Organization as orgCount where orgCount.Parent.Id=organization.Id) from Mimi.DM.Organization as organization where organization.Parent.Id=3]
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: NHibernate.QueryException: aggregate function expected before ( in SELECT [select organization.Name, organization.Id, (select count(orgCount.Id) from Mimi.DM.Organization as orgCount where orgCount.Parent.Id=organization.Id) from Mimi.DM.Organization as organization where organization.Parent.Id=3]

Source Error:


Line 119:
Line 120:
Line 121: IList children = session.Find("select organization.Name, organization.Id, (select count(orgCount.Id) from Mimi.DM.Organization as orgCount where orgCount.Parent.Id=organization.Id) from Mimi.DM.Organization as organization where organization.Parent.Id=" + node.Value);
Line 122:
Line 123: foreach (object[] a in children)


Source File: j:\development\Mimi\Mimi\i\Admin\Default.aspx.cs Line: 121

Stack Trace:


[QueryException: aggregate function expected before ( in SELECT [select organization.Name, organization.Id, (select count(orgCount.Id) from Mimi.DM.Organization as orgCount where orgCount.Parent.Id=organization.Id) from Mimi.DM.Organization as organization where organization.Parent.Id=3]]
NHibernate.Hql.SelectParser.Token(String token, QueryTranslator q) +492
NHibernate.Hql.ClauseParser.Token(String token, QueryTranslator q) +976
NHibernate.Hql.ClauseParser.End(QueryTranslator q) +178
NHibernate.Hql.PreprocessingParser.End(QueryTranslator q) +33
NHibernate.Hql.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q) +244
NHibernate.Hql.QueryTranslator.Compile() +160
NHibernate.Hql.QueryTranslator.Compile(ISessionFactoryImplementor factory, IDictionary replacements, Boolean scalar) +64
NHibernate.Impl.SessionFactoryImpl.GetQuery(String queryString, Boolean shallow) +134
NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar) +29
NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters) +123
NHibernate.Impl.SessionImpl.Find(String query) +70
i_Admin_Default2.LoadChildOrganizations(RadTreeNode node) in j:\development\Mimi\Mimi\i\Admin\Default.aspx.cs:121
i_Admin_Default2.NavTree1_NodeExpand(Object o, RadTreeNodeEventArgs e) in j:\development\Mimi\Mimi\i\Admin\Default.aspx.cs:157
Telerik.WebControls.RadTreeView.x01c1f3cfde26a4c5(RadTreeNodeEventArgs xfbf34718e704c6bc) +82
Telerik.WebControls.RadTreeView.x0b987a4af49b26e9(String xe4115acdf4fbfccc) +1153
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

I got the same error when I tried to use sub-query in a select list.
Rewrite your query to something like this:

select organization.Name, organization.Id, count(orgCount.Id)
from
Mimi.DM.Organization as organization,
Mimi.DM.Organization as orgCount
where
orgCount.Parent.Id=organization.Id and
organization.Parent.Id=3
group by organization.Name, organization.Id