I have a big and complex SQL query with a different section run in NHibernate (this is a search query with a wide variety)
meets criteria). But I'm having trouble using PageGrid with it under SQL Server 2008.
My question is: How can I do this from NHibernate and not my manual solution (mentioned below)?
The code I am using is:
var searchQuery = BuildQuery; .settimeout (240) .setcarscertelts (page index * pages) .custom results (pagesyes); Var resultRows = searchQuery.List & lt; Object [] & gt; (); The result of my SQL query (without the buildin of the Distinction) looks like this (highly simplified):
id, name, sort column 1a 1/1/2000 1AA 1/1/2000 2 AB 3/1/2000 2 AB 3/1/2000 3 AC 10/1/2000 3 AC 10/1/2000 .... I can not avoid duplicate rows due to how the query works, so I have to stay separate in the SQL query to remove them.
To mark the page, NHibernate generates such a query (this example is 100 pages per page result from another page):
SELECT TOP 100 From the Id, name, serial column, (different ID, name, sort column, ROW_NUMBER) over (sequence by sorted column) in the form of __hibernate_sort_row ...... ...... ...... WHERE query for query in .__ hibernate_sort_row & gt; 100 ORDER by query .__ hibernate_sort_row This works fine for the first page, but on subsequent pages, I get only half the estimated results.
I have found NHibernate subquery inside SQL and found that the line NHibernate inserts it to the arithmetic of the page gives some unexpected return (even different):
ID, name, serial column, __hibernate_sort_row 1aa 1/1/2000, 1 1aa 1 / 1/2000, 2 2db 3/1/2000, 3 2db 3/1/2000, 4 3ac10 When NHibernate wraps it into a subquery to filter it on the __hibernate_sort_row column, it should be 100 rows Returns to, but because every line dupl Iket is, I have actually only 50 lines on my page I reason that DISTINCT does not merge the lines as was I even expected. (It's all back to how the ROW_NUMBER function works with a separate section in SQL 2008, but this is NHibernate's problem, not mine.).
I have currently solved this problem by wrapping the query. Two subqueries and manually handling pagging:
SELECT TOP 100 paginationOuter * From (In the endorsement endorsement, by column ACC, sorted by. ROW_NUMBER ()) from Roam (Select a different ID, Name, Sortcolumn, ...... ...... ...... Where As paging endorsement page. Rowlum & gt; External from 100 points sequence Raoullum My question is: How can I do this with NHibernate and not my manual solution?
I am using NHibernate version 2.1.2. Unfortunately, I can not convert this query to HQL or LINQ (unless someone can supply me free time for a few weeks!). Neither can I remove DISTINCT from my query.
For anyone coming across this problem, it seems to be set in 3.3.1
Comments
Post a Comment