I recently started a new job and one of my first tasks was to implement a "popular products" design. The parameters were that it be done with NHibernate and be cached for 24 hours at a time because the query will be pretty taxing and the results do not need to be constantly up to date.

This ended up being tougher than it sounds.

The database schema meant a minimum of four joins with filtering and ordering criteria. I decided to use a stored procedure rather than letting NHibernate create the SQL for me. Here is a summary of what I learned (even if I didn't ultimately use all of it):

• You can't, at the time of this writing, use Fluent NHibernate to configure SQL named queries or imports
• You can return persistent entities from a stored procedure and there are a couple ways to do that
• You can populate POCOs using the results of a stored procedure, but it isn't quite as obvious
• You can reuse your named query result mapping other places (avoid duplication)
• Caching your query results is not at all obvious
• Testing to see if your cache is working is a pain
• NHibernate does a lot of things right. Having unified, up-to-date, comprehensive, and easy-to-find documentation is not one of them.

By the way, if you're new to this, I'll use the terms "named query" and "stored procedure" (from NHibernate's perspective) fairly interchangeably. Technically, a named query can execute any SQL, not just a stored procedure, and a stored procedure doesn't have to be executed from a named query, but for reusability, it seems to me like the best practice.

If you're here, chances are good you're looking for answers to a similar problem. You don't want to read about the path, you just want the result. So, here's how to get this thing going.

The Stored Procedure

NHibernate has some guidelines when using stored procedures. For Microsoft SQL Server, you have to return a result set. The scalar value that the stored procedure returns is ignored as are any result sets after the first. Other than that, it's nothing special.

CREATE PROCEDURE GetPopularProducts

    @StartDate   DATETIME,

    @MaxResults  INT

AS

BEGIN

    SELECT [ProductId],

           [ProductName],

           [ImageUrl]

      FROM SomeTableWithJoinsEtc

END

The Result Class - PopularProduct

You have two options to transport your query results to your view (or wherever is the final destination): you can populate an existing mapped entity class in your model, or you can create a new entity class. If you go with the existing model, the advantage is that the query will act as a loader and you'll get full proxied access to the domain model. However, this can be a disadvantage if you require access to the related entities that aren't loaded by your results. For example, my PopularProduct has image references. Unless I tie them into the query (thus making it even more complicated and expensive to run), they'll have to be loaded on access, requiring more trips to the database.

Since we're trying to avoid trips to the database by using a second-level cache, we should use the second option, which is to create a separate entity for results. This approach is (I believe) in the spirit of the Command-Query Separation principle, and it allows us to flatten our data and optimize our report-generation process from data source to view.

public class PopularProduct

{

    public virtual int ProductId { get; set; }

    public virtual string ProductName { get; set; }

    public virtual string ImageUrl { get; set; }

}

The NHibernate Mappings (hbm)

Next up, we need to let NHibernate know about the query and where the results will go. Below is the markup for the PopularProduct class. Notice that I'm using the <resultset> element and that it has a name attribute. The name allows us to drop this into our query map and any others, giving us reusability. Also notice the <import> element which lets NHibernate know about our entity class.

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

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

    <import class="PopularProduct, Infrastructure.NHibernate, Version=1.0.0.0"/>

    <resultset name="PopularProductResultSet">

        <return-scalar column="ProductId" type="System.Int32"/>

        <return-scalar column="ProductName" type="System.String"/>

        <return-scalar column="ImageUrl" type="System.String"/>

    </resultset>

</hibernate-mapping>

 

And now the PopularProductsMap:

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

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

    <sql-query name="GetPopularProducts" resultset-ref="PopularProductResultSet" 

               cacheable="true" cache-mode="normal">

        <query-param name="StartDate" type="System.DateTime" />

        <query-param name="MaxResults" type="System.Int32" />

        exec GetPopularProducts @StartDate = :StartDate, @MaxResults = :MaxResults

    </sql-query>

</hibernate-mapping>

 

The two most important things to notice here are the

resultset-ref

attribute, which links in our resultset mapping, and the

cacheable

attribute.

The Query Class – PopularProductsQuery

So far, this has been fairly obvious if you're familiar with NHibernate. This next part, maybe not so much. You can implement your query however you want to; for me, I wanted a self-encapsulated Query class, so here's what it looks like:

public class PopularProductsQuery : IPopularProductsQuery

{

    private static readonly IResultTransformer ResultTransformer; 

    private readonly ISessionBuilder _sessionBuilder;

 

    static PopularProductsQuery()

    {

        ResultTransformer = Transformers.AliasToBean<PopularProduct>();

    }

 

    public PopularProductsQuery(ISessionBuilder sessionBuilder)

    {

        _sessionBuilder = sessionBuilder;

    }

 

    public IList<PopularProduct> GetPopularProducts(DateTime startDate, int maxResults)

    {

        var session = _sessionBuilder.GetSession();

        var popularProducts = session

                                 .GetNamedQuery("GetPopularProducts")

                                 .SetCacheable(true)

                                 .SetCacheRegion("PopularProductsCacheRegion")

                                 .SetCacheMode(CacheMode.Normal)

                                 .SetReadOnly(true)

                                 .SetResultTransformer(ResultTransformer)

                                 .SetParameter("StartDate", startDate.Date)

                                 .SetParameter("MaxResults", maxResults)

                                 .List<PopularProduct>();

 

        return popularProducts;

    }

}

 

Okay, so let's look at each line of the query execution. The first,

GetNamedQuery

, matches up with our NHibernate mapping for the sql-query. Next, we set it as cacheable (this is probably redundant since our mapping also specified it, but it can't hurt, right?). Then we set the cache region which we'll get to in the next section. Set the cache mode (optional, I believe), and my cache is read-only, so I set that as well. The result transformer is very important. This tells NHibernate how to transform your query results into a non-persistent entity. You can see I've defined

ResultTransformer

in the static constructor using the AliasToBean transformer. The name is obviously leftover from Java/Hibernate. Finally, set your parameters and then call a result method which will execute the query.

Because this is set to cached, you execute this statement every time you run the query and NHibernate will know based on your parameters whether to use its cached version or a fresh version.

The Configuration – hibernate.cfg.xml and Web.config

You need to explicitly enable second-level caching in your hibernate configuration:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

    <session-factory>

        [...]

        <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>

        <property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider,NHibernate.Caches.SysCache</property>

        <property name="cache.use_query_cache">true</property>

        <property name="cache.use_second_level_cache">true</property>

        [...]

  </session-factory>

</hibernate-configuration>

Both properties "use_query_cache" and "use_second_level_cache" are necessary. As this is for a web deployement, we're using SysCache which relies on ASP.NET's caching. Be aware of this if you're not deploying to the web! You'll have to use a different cache provider.

We also need to tell our cache provider (in this cache, SysCache) about our caching region:

<syscache>

    <cache region="PopularProductsCacheRegion" expiration="86400" priority="5" />

</syscache>

Here I've set the cache to be valid for 24 hours. This XML snippet goes in your Web.config (or in a separate file referenced by Web.config, which helps keep things tidy).

The Payoff

That should be it! At this point, your queries should run once against the database for a given set of parameters and then use the cache thereafter until it expires. You can, of course, adjust settings to work in your particular environment.

Testing

Testing your application to ensure it is using the cache is a pain, but if you're like me, you want to know that it's actually working. It's a bit involved, though, so I'll create a separate post for it if comments indicate there is interest.