Back to basics – paging pe SQL Server 2012

Aminteam într-un post mai vechi că SQL Server, în sfârșit (incepând cu viitorea versiune Denali/2012 (fost 2011 la un moment dat), actualmente în stadiul de CTP3) suportă clauzele OFFSET/FETCH pentru paginare, metodă existentă în standardul SQL de o gramadă de vreme.

Cum, din câte știu Entrity Framework 4 încă nu are suport pentru OFFSET/FETCH și nici nu oferă un extensibility point ușor de folosit pentru așa ceva, să vedem cum putem adăuga suport în NHibernate 3 pentru SQL Server Denali/2012 CTP3 și offset/fetch.

Sculele necesare:
– Download și install SQL Server ‘Denali’ Express Core Community Technology Preview 3 (CTP 3) sau o versiune mai recentă (si versiunea “full” e ok, dar hai să fim minimaliști).
Ne mai trebuie un sample database, de ex. bătrânul Adventure Works (în versiunea actuală e decent ca structură).

– Download și install NHibernate 3.2 .
– se subînțelege: Visual Studio 2010, de ex.
Dacă vrem să folosim SQL Server Management Studio pentru Denali Express CTP3, ne trebuie și SP1 pentru VS2010 (chiar dacă e deja instalat, Denali CTP3 zăpăcește lucrurile și SP1 va trebui reinstalat: http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx).

După ce ne asigurăm că instanța de SQL Server e pornită și o putem accesa, we are ready to go.

Într-un simplu console application, configurăm NHibernate (http://nhforge.org/wikis/howtonh/your-first-nhibernate-based-application.aspx),
în hibernate.cfg.xml setăm connection string-ul și cel mai recent dialect:

<?xml version="1.0" encoding="utf-8"?>

<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory>
...
    <property name="connection.connection_string">
      Data Source=localhost\SQLEXPRESS2012;Initial Catalog=AdventureWorks2008R2;Integrated Security=SSPI;
    </property>
...
    <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
...
  </session-factory>
</hibernate-configuration>

și, în sfârșit puten scrie un query care să ne aducă un page din tabela Person:

static IList<Person> GetPersonsPage(int pageNumber, int pageSize)
{
  IList<Person> persons = null;

  using (ISession session = _sessionFactory.OpenSession())
  {
  persons = session.QueryOver<Person>()
                     .OrderBy(p => p.LastName).Asc
                     .Skip(pageNumber * pageSize) .Take(pageSize)
                     .List();
  }

  return persons;
}

Cum mă astepam, NHibernate nu are de unde să știe ca SQL Server Denali (mai nou 2012) stie de OFFSET sau FETCH, și generează urmatorul query:

SELECT TOP (@p0) 
  Business1_0_0_, PersonType0_0_, NameStyle0_0_, Title0_0_, FirstName0_0_, MiddleName0_0_, LastName0_0_, EmailPro8_0_0_, Modified9_0_0_
FROM (
    SELECT this_.BusinessEntityID as Business1_0_0_, this_.PersonType as PersonType0_0_, this_.NameStyle as NameStyle0_0_, this_.Title as Title0_0_, this_.FirstName as FirstName0_0_, this_.MiddleName as MiddleName0_0_, this_.LastName as LastName0_0_, this_.EmailPromotion as EmailPro8_0_0_, this_.ModifiedDate as Modified9_0_0_
    , ROW_NUMBER()
    OVER(ORDER BY this_.LastName) as __hibernate_sort_row
    FROM Person.Person this_) as query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row

@p0=30,@p1=330

… clasicul ROW_NUMBER/OVER cu subselecturi introdus în SQL Server 2005… Nu e rău, însă doar un dbadmin ar putea să iubescă sintaxa asta.

Să vedem dacă putem face ceva în sensul ăsta…
Mergem la https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Dialect/MsSqlCe40Dialect.cs
și aruncăm o privire cum a fost implementat suportul de paging cu OFFSET/FETCH  pentru SQL Server CE 4.0 – cu puțin noroc ar trebui să putem re-folosi clasa respectivă neschimbată 🙂

Cum totuși SQL Server CE diferă pe ici-colo față de SQL Server-ul ‘normal’, vom crea o clasa MsSql2012Dialect, derivată din clasa MsSql2008Dialect, în care vor folosi o implementare identică cu cea din MsSqlCe40Dialect – normal, copy-paste code reuse nu e o soluție elegantă, deci în viitor ne așteaptă un refactoring.
Derivarea din MSSql2008Dialect e o soluție facilă, dar pleacă de la prepunerea că viitoarea versiune de SQL Server e backward-compatible

Punem noua clasă intr-un assembly separat (class library) și obținem:

using NHibernate.Dialect.Function;
using NHibernate.SqlCommand;

namespace NHibernate.Dialect
{
    public class MsSql2012Dialect : MsSql2008Dialect
    {
        public MsSql2012Dialect()
        {
            RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
        }

        public override bool SupportsLimit
        {
            get { return true; }
        }

        public override bool SupportsLimitOffset
        {
            get { return true; }
        }

        public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
        {
            SqlStringBuilder builder = new SqlStringBuilder(queryString);
            if (queryString.IndexOfCaseInsensitive(" ORDER BY ") < 0)
                builder.Add(" ORDER BY GETDATE()");

            builder.Add(" OFFSET ");
            if (offset == null)
                builder.Add("0");
            else
                builder.Add(offset);
            builder.Add(" ROWS");

            if (limit != null)
            {
                builder.Add(" FETCH NEXT ");
                builder.Add(limit);
                builder.Add(" ROWS ONLY");
            }

            return builder.ToSqlString();
        }
    }
}

Ca să putem folosi noua clasă în proiectul nostru, tot ce mai rămane de facut e să adaug o referință la assembly și să modific hibernate.cfg.xml pentru a referi noul dialect:

<property name="dialect">NHibernate.Dialect.MsSql2012Dialect, NHibernate.Dialect.MsSqlServer2012</property>

( classname, assemblyname)

Rulez aplicația și NHibernate va genera o frumusețe de SQL:

SELECT this_.BusinessEntityID as Business1_0_0_, this_.PersonType as PersonType0_0_, this_.NameStyle as NameStyle0_0_, this_.Title as Title0_0_, this_.FirstName as FirstName0_0_, this_.MiddleName as MiddleName0_0_, this_.LastName as LastName0_0_, this_.EmailPromotion as EmailPro8_0_0_, this_.ModifiedDate as Modified9_0_0_
FROM Person.Person this_
ORDER BY this_.LastName asc
OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY

@p0=330,@p1=30

Execution plan-ul pe server va fi aproape același (http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/10/sql-server-11-denali-using-the-offset-clause.aspx) însă SQL-ul generat e mult mai lizibil și elegant.

Soluția e doar una “temporară” din mai multe motive:
– cod duplicat cu SQL Server CE4 dialect
– posibil conflict de nume când NHibernate va include în versiunile viitoare suport pentru SQL Server 2012
– no unit tests (yet)
– frumos ar fi ca acest cod în măsura în care se dovedește util să ajungă înpoi în NHibernate (open source spirit 🙂

Morala postului ăsta ar fi: bine ar fi ca Entity Framework să fie la fel de modular și usor de extins..

P.S.: un unit test copiat cu nerușinare de la https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate.Test/DialectTest/MsSqlCe40DialectFixture.cs
as arăta cam așa:

using NHibernate.Dialect;
using NHibernate.SqlCommand;

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace NHibernate.Test.DialectTest
{
    [TestClass]
    public class MsSql2012DialectTest
    {
        [TestMethod]
        public void GetLimitString()
        {
            var dialect = new MsSql2012Dialect();
            var str = dialect.GetLimitString(new SqlString("SELECT id FROM user ORDER BY name"), new SqlString("13"), new SqlString("17"));
            Assert.AreEqual("SELECT id FROM user ORDER BY name OFFSET 13 ROWS FETCH NEXT 17 ROWS ONLY", str.ToString());
        }

        [TestMethod]
        public void GetLimitStringWithDummyOrder()
        {
            var dialect = new MsSql2012Dialect();
            var str = dialect.GetLimitString(new SqlString("SELECT id FROM user"), new SqlString("13"), new SqlString("17"));
            Assert.AreEqual("SELECT id FROM user ORDER BY GETDATE() OFFSET 13 ROWS FETCH NEXT 17 ROWS ONLY", str.ToString());
        }
    }
}

Advertisements
This entry was posted in .NET, NHibernate, SQL Server and tagged , , , , . Bookmark the permalink.

4 Responses to Back to basics – paging pe SQL Server 2012

  1. Skorpion King says:

    un pic de rabdare pana la .NET Framework 5.0 si Entity Framework 5.0. Cu putin noroc daca baietii baga mare, va aparea chiar cu .NET Framework 4.5. asa ca no need for NHibernate 😛

  2. Tudor Turcu says:

    Să înțeleg ca EF5 va fi suficient de flexibil a.î. să pot face singur astfel de customizări pentru alte database servers în viitor? 🙂

  3. Andrei Rinea says:

    un alt motiv sa “iubesc” ORM-urile…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s