using nhibernate t-sql table-valued user-defined functions

Question

Is it possible to use table t-sql table-valued user-defined function as part of HQL query ?

here Nhibernate filtering by user defined function output is a example how to create custom dialect extention but in this example scalar UDF is used.

Let's assume that I have following t-sql UDT

CREATE FUNCTION [dbo].[getSuitableProjects]  
(
    @userID INT  
)  
RETURNS @result TABLE   
(  
    ProjectID INT  
)  
AS  
-- body of the function ..

I would like to be able to write following hql - second line is pseudocode, simply I would like to invoke my function as part of hql query - real query is more complicated it has fetch joins etc.

StringBuilder hql = new StringBuilder();  
hql.AppendLine("select p from Projects p WHERE p.ProjectID IN");  
hql.AppendLine("(run with parameter -> getSuitableProjects(:userID))");

IQuery q = UOW.Session.CreateQuery(hql.ToString());  
q.SetInt32("userID", userID);

I spend some time looking for answer but without results. Is it possible ?
Thank You in Advance.

I had a doubt similiar to yours, have a look on this link stackoverflow.com/questions/30812235/… — rcarvalhoxavier
– rcarvalhoxavier, Commented Jun 15, 2015 at 14:10

Diego Mijelshon · Accepted Answer · 2013-01-06 00:51:57Z

0

No, you can't use a table-valued function with HQL.

Use SQL (CreateSQLQuery(...)) instead.

answered Jan 6, 2013 at 0:51

Diego Mijelshon

52.7k16 gold badges119 silver badges159 bronze badges

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

Comments

Krzysiek · Accepted Answer · 2013-01-06 18:29:15Z

0

Maybe someone will find it usefull, I've ended with following query

var query = UOW.Session.QueryOver<Project>();
query = query.Where(...) //where  citeria which can be expressed in QueryOver
query = query.Fetch(z => z.Items).Eager.TransformUsing(Transformers.DistinctRootEntity);

var criteria = query.UnderlyingCriteria
.Add(Expression.Sql("{alias}.ID IN (SELECT ProjectID FROM dbo.getSuitableProjects(12))"))

My goal was to have items preloaded (z.Items) and simultaniously to restrict to only those projects which are available for user -> this logic sits in my table value UDF.

answered Jan 6, 2013 at 18:29

Krzysiek

3382 silver badges10 bronze badges

Collectives™ on Stack Overflow

using nhibernate t-sql table-valued user-defined functions

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related