Using a named query (or directly a table valued function) from QueryOver queries

[xanatos]

I have some table valued functions with parameters. I use them to do some recursive query and some other queries based on "things" not supported by nhibernate (for example OVER PARTITION BY). They return data in the same format as a SELECT * FROM SomeTable where SomeTable is correctly mapped in NHibernate.

I want to include this TVF (or the named query that I use for it) in a QueryOver query, for example something like

    var q = QueryOver.Of<SomeTable>(myTableFunction).Where(p => p.Id > 10);

but I don't know how to connect the Session.GetNamedQuery("myquery") that returns an IQuery with the QueryOver, or how to create a Projection.SqlFunction that encapsulates my TVF and use it... Or any other way....

Thanks

[ggeurts]

The way to go here is to use something like

 

var q = session

    .CreateSQLQuery(@"SELECT * FROM dbo.MyTableFunction() WHERE p.Id > :MinId)

    .AddEntity(typeof(SomeTable))

    .SetInt64("MinId", 10);

[xanatos]

Where is the QueryOver here? I was not asking how to use a table function in NHibernate, my question was "how to use it with QueryOver<>".

[xanatos]

Finally I was able to find something:

    var query = Expression.Sql(SqlString.Parse("EXISTS (SELECT 1 FROM dbo.MyFunction(?, ?) MF WHERE MF.ID = {alias}.[ID])"), new object[] { param1, param2 }, new[] { NHibernateUtil.Int32, NHibernateUtil.Int32 });

    var res = Session.QueryOver<Nodo>()

        .Where(query)

        .Where(.. other conditions ...)

        .List();

Clearly the number and the type of the parameters can be changed. The table function must return a list of IDs. This doesn't work if the table function has to add extra data (so, for exampe, if the TVF calculated a rolling total and returned it then you can't use this method). You can use this method only if you use the TVF as a "selector" for which rows to return. I could have done the query using the IN clause (something like {alias}.[ID] IN (SELECT ID FROM dbo.MyFunction(?, ?)), but that doesn't work if you have a composite key, while the EXISTS variant does (in the end what I have done can be put only in the WHERE clause, and not in a JOIN)