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

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):

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <hibernate-mapping namespace="Succinctly.Model" assembly="Succinctly.Model" xmlns="urn:nhibernate-mapping-2.2">
   3:     <class name="BlogPost" mutable="false">
   4:         <subselect>
   5:             <![CDATA[
   6:             SELECT blog_id, (SELECT COUNT(1) FROM post WHERE post.blog_id = blog.blog_id) post_count FROM blog
   7:             ]]>
   8:         </subselect>
   9:         <synchronize table="blog"/>
  10:         <synchronize table="post"/>
  11:         <id column="blog_id" name="BlogId"/>
  12:         <property name="PostCount" column="post_count"/>
  13:     </class>
  14: </hibernate-mapping>

In mapping by code, it would be:

   1: public class BlogPostMapping : ClassMapping<BlogPost>
   2: {
   3:     public BlogPostMapping()
   4:     {
   5:         this.Subselect("SELECT blog_id, (SELECT COUNT(1) FROM post WHERE post.blog_id = blog.blog_id) post_count FROM blog");
   6:         this.Mutable(false);
   7:         this.Synchronize("blog", "post");
   8:         this.Id(x => x.BlogId, x => x.Column("blog_id"));
   9:         this.Property(x => x.PostCount, x => x.Column("post_count"));
  10:     }
  11: }

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:

   1: select
   2:     blogpost0_.blog_id as blog1_19_,
   3:     blogpost0_.post_count as post2_19_
   4: from
   5:     ( SELECT
   6:         blog_id,
   7:         (SELECT
   8:             COUNT(1)
   9:         FROM
  10:             post
  11:         WHERE
  12:             post.blog_id = blog.blog_id) post_count
  13:     FROM
  14:         blog ) blogpost0_


No Comments

Add a Comment

As it will appear on the website

Not displayed

Your website