Lesser-Known NHibernate Features: Mapping a Class to a Query

Monday, October 6, 2014

.net NHibernate orm

Today I start a new series on lesser-known NHibernate features.

Besides the normal mapping of a class to a table (or view, for that matter), it is also possible to map a class to a query. Do note that this is not the same as having a class mapped to a table and using custom SQL for the INSERTs, UPDATEs, SELECTs or DELETEs, this is something different. This is called Subselect mapping - not to be confused with subselect fetching.

To demonstrate this, imagine we have a blogging model where a Blog has several Posts. We can map a readonly query between the two as (in HBM.XML):

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

<hibernate-mapping namespace="Succinctly.Model" assembly="Succinctly.Model" xmlns="urn:nhibernate-mapping-2.2">

    <class name="BlogPost" mutable="false">

        <subselect>

            <![CDATA[

            SELECT blog_id, (SELECT COUNT(1) FROM post WHERE post.blog_id = blog.blog_id) post_count FROM blog

            ]]>

        </subselect>

        <synchronize table="blog"/>

        <synchronize table="post"/>

        <id column="blog_id" name="BlogId"/>

        <property name="PostCount" column="post_count"/>

    </class>

</hibernate-mapping>

In mapping by code, it would be:

public class BlogPostMapping : ClassMapping<BlogPost>

{

    public BlogPostMapping()

    {

        this.Subselect("SELECT blog_id, (SELECT COUNT(1) FROM post WHERE post.blog_id = blog.blog_id) post_count FROM blog");

        this.Mutable(false);

        this.Synchronize("blog", "post");

        this.Id(x => x.BlogId, x => x.Column("blog_id"));

        this.Property(x => x.PostCount, x => x.Column("post_count"));

    }

}

It easy to understand that the class cannot be mutable: it makes no sense to change any of the properties, because they may not map directly to a table.

Querying is done in exactly the same way:

   1: session.Query<BlogPost>().ToList();

And the resulting SQL is:

select

    blogpost0_.blog_id as blog1_19_,

    blogpost0_.post_count as post2_19_

from

    ( SELECT

        blog_id,

        (SELECT

            COUNT(1)

        FROM

            post

        WHERE

            post.blog_id = blog.blog_id) post_count

    FROM

        blog ) blogpost0_