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);

I don't think you can do it like this - you'd have to return the result of idsq to the client, which is probably a bad idea as you don't know how large it is. You can probably add [dbo].[SomeFunction] to a custom sql dialect and use it that way - see nhibernate.info/blog/2009/03/13/… or ayende.com/blog/1720/using-sql-functions-in-nhibernate — kͩeͣmͮpͥ ͩ
– kͩeͣmͮpͥ ͩ, Commented May 15, 2018 at 13:03
I don't think you can easily do it... The "direction" should be: .WithSubquery.WhereProperty(x => x.Id).In(...) but then the In requires a QueryOver query. — xanatos
– xanatos, Commented May 15, 2018 at 13:04
I dont' know why NH cant handle this case, because it should be easy to embed native SQL inside a where in () in generated sql or even join to table valued sql function — Baloo0ch
– Baloo0ch, Commented May 15, 2018 at 13:09
Also see andrewwhitaker.com/blog/2014/08/15/… which suggests using Projections.SqlFunction — kͩeͣmͮpͥ ͩ
– kͩeͣmͮpͥ ͩ, Commented May 15, 2018 at 13:10

xanatos · Accepted Answer · 2018-05-15 13:45:53Z

3

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 (...)

edited May 15, 2018 at 13:45

answered May 15, 2018 at 13:15

xanatos

112k13 gold badges209 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Baloo0ch Over a year ago

Thanks So Much, It worked perfectly, I think this is a must to have trick for legacy database

Baloo0ch Over a year ago

NHibernate use function in another one to many query for a One-To-Many association between MyEntity and another child collection table, Do you have any suggestion to make it one time call without producing Cartesian production result

xanatos Over a year ago

@HamidNaeemi Unclear what you are asking... and it seems to be quite complex... you should make another question.

Collectives™ on Stack Overflow

Nhibernate SQLQuery as Subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related