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 !