Hello all. I recently started using named HQL queries and I think I've run into my first problem. I have an HQL query that is in my mapping file, I want the Sort Field and Sort Direction to be parametrized (see below). When i execute my query without the parametrized Order By clause it works perfect, with the Order By clause I received an "Index Out Of Range" exception (Target = PopulateSqlString).

FROM
Ticket as ticket
LEFT JOIN FETCH ticket.IntranetUser
LEFT JOIN FETCH ticket.Department
LEFT JOIN FETCH ticket.Branch
WHERE
ticket.IntranetUserID = :userID
AND
ticket.TicketStatus = :ticketStatus
ORDER BY :sortField :sortDirection

I could not find any documentation stating that named parameters were not valid in the context of the Order By clause. Has anyone else attempted to do this? Any help would be greatly appreciated, I'm currently digging through the NHibernate source to see if i can't determine if this is intentional or a bug.

Here is the code that is executing the query (with named parameters for the order by clause).

IList ut;

ISession s = _sessionFactory.OpenSession();

ITransaction t = s.BeginTransaction();

try
{
IQuery q = s.GetNamedQuery("GetUserTickets");

q.SetInt32("userID", intranetUserID);
q.SetEnum("ticketStatus", status);
q.SetString("sortField", sortField);
q.SetString("sortDirection", GetSortDirectionString(sortDirection));

ut = q.List();

t.Commit();
}
catch (Exception ex)
{
t.Rollback();

throw new ApplicationException("An exception occured while trying to get user tickets.", ex);
}
finally
{
s.Close();
}

return ut;

Thanks in advance.

Ok, after downloading the source and debugging i've come to the conclusion the named parameters are 1-1 with command parameters (SqlClient in my case). So unfortunately adding parameters can only be done when assigning values to columns.

Here is the code.

for( int i = 0; i < paramTypeList.Count; i++ )
{
IType type = ( IType ) paramTypeList[ i ];
string[ ] colNames = new string[type.GetColumnSpan( factory )];

for( int j = 0; j < colNames.Length; j++ )
{
colNames[ j ] = "p" + paramIndex.ToString() + j.ToString();
}

Parameter[ ] sqlParameters = Parameter.GenerateParameters( factory, colNames, type );

foreach( Parameter param in sqlParameters )
{
sqlPartIndex = paramIndexes[ paramIndex ];
sql.SqlParts[ sqlPartIndex ] = param;

paramIndex++;
}
}

Bah. I'll try a different way.

the only way i've been able to do this is with inline IQuery objects:



although I've wondered if you could do something like:



seems like a hack, but i was looking for a way to get the benefit of keeping the main part of the query in the mapping file...

Hi, thanks for the solution, I'm with this issue for about a two hours, but, the code that you list don´t works for me (I had to use a inline query) :(.

Do you post this issue in the bug tracking of NHibernate (JIRA)?, is this solved?

Regards.