We are using version 3.3.2 GA (From Nuget)
I have what I think is a complex query, that allows our users to search the
system using quite a few different switches. We implemented this using the
QueryOver<T> API. We are also supporting paging and are required to get two
different object counts, so we have utilized the Future<> support to limit
the trips back to the database. I have ran into a case when I add a
Criteria such as (This works fine without using the Future):
var crit = CurrentSession.QueryOver<WorkBase>(() => opp);
/* Snipped out adding various other criteria, paging ordering and
projecting into the alias*/
*crit.Where(Expression.Sql("({alias}.Organizationid in (SELECT * FROM
[fn_Get_Organization_Search_Tree](?)))", query.OrganizationId, new
NHibernate.Type.GuidType()));*
I end up getting SQL which generates the error: The parameterized query
'(@p0 bit,@p1 bit,@p2 int,@p3 uniqueidentifier,@p4 int,@p5 int,@p' expects
the parameter '@p5', which was not supplied.
The query that is generated that is a multiquery looks like this (I snipped
out what I feel is irrelevant)
SELECT count(*) as y0_
FROM WorkAlert this_ /*snipped*/;
SELECT count(*) as y0_
FROM Work /* snipped */
WHERE this_.FolderId is null and this_.Status = @p4
and (this_.Organizationid in (SELECT * FROM
[fn_Get_Organization_Search_Tree](*@p5*)));
SELECT TOP (*@p5*) /* snipped fields */
FROM Work this_
/* snipped other joins*/
WHERE this_.FolderId is null and this_.Status = @p12 and
(this_.Organizationid in (SELECT * FROM
[fn_Get_Organization_Search_Tree](@p13))) ORDER BY y25_ desc, y24_ desc,
this_.DateCreated desc;
In this query @p5 is defined as an int with the value of default being
passed in. (I am passing to nhibernate a page size of 25 FYI). My Search
Tree function takes a GUID and is correct in the third query. If I'm able
I will try and create a reproduction. If anyone can provide any insight or
ideas....Is there a way I can get rid of the SQLExpression? The function
being used traverses a tree finding all ancestors and descendants, I
couldn't figure out a way to handle that without using a SQLExpression.
I also ran into another issue where my Work object was an abstract object
and the Alias in the SQL Expression wouldn't resolve the base alias, but
the object was only abstract due to an outdated design and we refactored it
and smashed it.
Thanks for any help!
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/juCxHXvL78QJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
