فنی

Paging problem with Named SQL queries

توسط در شنبه, نوامبر 27, 2010

Named SQL query in NHibernate is an easy way to apply paging through native SQL. SetFirstResult and SetMaxResults helps a lot here. But the problem is when query is get a bit complicated or for some other reasons that I don’t know, paging does not works on pages after first page. In this case a wrong sql query is generated.

From given answers I didn’t realized what’s the root cause and if there is any solutions or not. But because I was in emergency I used my work-around for it.

My work-around is adding paging elements directly in the query and pass “first result” and “max results” to the query as parameters instead of using SetFirstResult and SetMaxResults. Consider following example:





                   default-access="field.camelcase"
namespace="nhtest"
assembly="nhtest">

  
    
    SELECT TOP (:maxResults) * FROM (

select 

*

,ROW_NUMBER() OVER(ORDER BY (:orderBy)) as _sort_row from 
  
Toy

)as query WHERE query._sort_row > :firstResult 
ORDER BY query._sort_row

]]>



This code is inspired from script that NHibernate itself produces while correct paging. Parametrization “top” and “order” sections was not very easy. I got helped from here and here. Notice parametrized “order” is not necessary for paging. It was my own requirement.

106
پست های مرتبط

Comments

  1. ناشناس
    پاسخ

    I don't know if NHibernate supports "Take" and "Skip" methods (Linq Extension methods). But if they supported it will be so easy to use them instead. Isn't it?!

  3. Afshar Mohebbi
    پاسخ

    Yes, Linq-to-NHibernate supports Take/Skip extension methods and their work great. But as mentioned in the text, I'm using Named SQL queries not linq-to-nh. Named SQL queries is a way you can run native SQL queries in NHibernate.

  5. Afshar Mohebbi
    پاسخ

    It's a legacy SQL query that the BOSS does love to convert them to linq-to-nh. Additionally linq-to-nh (2.1.2) has many limitations. One that bothered me a lot is it does not support "distinct" method. Additionally it has restrictions about sub queries.

  8. ناشناس
    پاسخ

    Easybinarycash.com – Learn how to turn $500 into $5,000 in a month!

    [url=http://www.easybinarycash.com/]Make Money Online[/url] – The Secret Reveled with Binary Option

    Binary Options is the way to [url=http://www.easybinarycash.com/]make money[/url] securely online

  89. ناشناس
    پاسخ

    Concern Fresh Wind offers a invest money in building for different tariff plans. Today , overcame the recent financial crisis, more began to wonder where better to invest. According to conclusions of many of the leading the best analysts, one of the most investing money in building. Everyone knows that the construction of less exposed to the risk of falling prices, in addition a given that, property has always enjoyed , and will be in demand. Even the economic crisis did not recaptured removed the desire for people to buy their own property. Therefore, investment in construction will always remain relevant and profitable.

    Fwit Biz – [url=https://fwit.biz]in which bank to invest[/url]

  105. ناشناس
    پاسخ

    up buy Aceon online without prescription If discussing Cialis's side effects, you should remember that there are extremely much correspond to to those of Viagra and Levitra. The most noted ones are dealing with gastralgia, insidiously a overcome http://trustfromcanada.com/catalog/Blood_Pressure/Aceon-free-shipping.htm#2ggt A not many days underwrite, a recent bus of a prep train landed in the regulate dragnet in Virginia Beach. He was trying to establish a lustful relations with a teenage boy. In the form week of [url=http://trustfromcanada.com/catalog/Blood_Pressure/Aceon-free-shipping.htm#yyg1]special offer buy Aceon online[/url] znmjmxuh

  106. ناشناس
    پاسخ

    9p special offer buy Lisinopril online at a simple affordable cost. It's nearby in assorted online shops or men's internet sites. When applying Clomid, the sportsmen chassis revealed 2 situations – block unwanted side effects of estrogen and consequently shove testosterone amount organically. http://trustfromcanada.com/catalog/Blood_Pressure/Lisinopril-free-shipping.htm#h946 Viagra is sold as spoken tablets of 25mg, 50mg and 100mg strengths, each priced at all about US$ 10/pill. It is mainly advised to start with the middle strength and then move up or [url=http://trustfromcanada.com/catalog/Blood_Pressure/Lisinopril-free-shipping.htm#pq3m]Lisinopril Trust From Canada[/url] zc3burqb

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *