Nhibernate SQLQuery as Subquery

Question

How Can use a native sqlquery (session.CreateSqlQuery) as filtering subquery in another QueryOver:

                // Get  ids
            var idsq = _session.CreateSQLQuery(
                "select Id from [dbo].[SomeFunction](:parameter)")
                .AddEntity(typeof(long)).
                SetParameter("parameter", folderId);

            // get entities by filtering where in (subquery)
            MyEntity entityAlias = null;
            var listOfEntities = await _session.QueryOver(() => entityAlias).
                Where(x=>x.Id).IsIn(idsq).
                OrderBy(x => x.Name).Asc.
                ListAsync(cancelToken).ConfigureAwait(false);

Answer 1

You can't easily mix various styles of NHibernate... What you can do:

var crit = new SQLCriterion(SqlString.Parse("{alias}.Id IN (select Id from [dbo].[SomeFunction](?))"),
                                    new object[] { folderId },
                                    new IType[] { NHibernateUtil.Int64 });

and then:

var listOfEntities = await _session.QueryOver(() => entityAlias)
    .Where(crit)
    .OrderBy(x => x.Name).Asc

Note how I changed the text query adding {alias}.Id IN (...)