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_