Dario Quintana

At chat with Fabio Maulo, he tell me about the goodness of use Named Queries with NHibernate and that it’s the reason for this post.

What is Named Query ? It’s a Query that are defined at mapping files and have a unique name, could be
in HQL or Native SQL.

A HQL named query could be written like this:

  <query name="Foo-by-name" cacheable="false" read-only="true">
    <![CDATA[
        from Foo where Name like :value
    ]]>
  </query>

And the equivalent named query for Native SQL could be written in this way:

  <sql-query name="Foo-by-name" cacheable="false" read-only="true">
    <return alias="foo" class="Foo"/>
    <![CDATA[
    SELECT
    {foo}.ID AS {foo.Id},
    {foo}.NAME AS {foo.Name},
    {foo}.MISC AS {foo.Misc}
    FROM Foo {foo}
    WHERE {foo}.Name LIKE :value
    ]]>
  </sql-query>

The code that execute the query:

    Foo f1 = new Foo();
    f1.Misc = "misc 1";
    f1.Name = "foo 1";
    session.Save(f1);

    Foo f2 = new Foo();
    f2.Misc = "misc 2";
    f2.Name = "foo 2";
    session.Save(f2);

    session.Flush();

    IQuery q = session.GetNamedQuery("Foo-by-name");
    q.SetString("value", "f%");

    Debug.Assert(q.List().Count == 2);

As you see, both named queries are executed with the same code

Advantages:

• If we change the RDBMS, we must change the native query at mapping instead of code.
• If we have HQL queries and we want use a special RDBMS feature, we only must write the equivalent query at Native SQL and do not touch the code.
• Very Important! And practicly… the reason for this post. The named queries are parsed once when you call to .BuildSessionFactory() and used forever! So that if we write a wrong query (i.e. mistake on a property name), NHibernate make the advice throwing an exception. The queries that you write at code (non-Named Queries) are parsed all the time unless cached. NHibernate now provide a MRU cache for the non-Named Queries of 128 positions, to parse queries the least amount of times.
• Clean code !
• Avoid build handcraft queries, making use and abuse of String.Format(…), and wasting positions at the MRU cache of course.

Thanks Fabio for the info !