There are some things that you simply can't do effectively with an abstraction. Take Hierarchical Queries as a good example. They are very hard in SQL, there is no good way to solve the issue in a portable way. Therefor, each database has its own method to handle this issue. In SQL 2000, you would build a table valued function that you would query, in Oracle, you use CONNECT BY, and in SQL 2005 and DB2 (that I know of) you can use Common Table Expressions.
Out of the box, NHibernate can't handle this, but is exposes the hooks to allow this. A classic example is bringing all the messages in a thread in a single query. This is an issue since if you try to handle this by default, you'll get a query per each level in the hierarchy. Here is a simple message class, which has a parent message.
And here is how we get all the messages in the hierarchy, for the message with id 43:
SimpleQuery<Message> msgs = new SimpleQuery<Message>(QueryLanguage.Sql, @"
WITH AllMsgs ( id )
AS ( SELECT messages.id FROM Messages
WHERE messages.id = ?
UNION ALL
SELECT messages.id FROM Messages
INNER JOIN AllMsgs parent
ON messages.parent = parent.id )
SELECT {msg.*}
FROM Messages msg
WHERE msg.id IN ( SELECT id FROM AllMsgs )
", 43);
msgs.AddSqlReturnDefinition(typeof(Message),"msg");
Message[] messages = msgs.Execute();
The SQL query is standard SQL for SQL Server 2005, with two slight modifications. The ? marks a positional parameter, in this case, the 43 that we pass to the query. And the {msg.*}, which NHibernate will expand for you to the appropriate columns. It is important to notice that we need to tell the query the mapping between the Message type and the "msg" alias.
We brought all the message hierarchy to the application in one shot, instead of N+1. This is really good, if you are interested only in the properties of the message. A slight complication may arise if you want to handle the author for each message. Now we need to bring not only use the messages, but their authors, preferably in a single query. Let us see how we tackle this.
We will want to pull everything in one go, which means that we need to join between the messages and users table. This, in turn, means that we will get a list of tuples back. Here is the query itself:
SimpleQuery<object[]> msgs = new SimpleQuery<object[]>(typeof(Message),QueryLanguage.Sql, @"
WITH AllMsgs ( id )
AS ( SELECT messages.id FROM Messages
WHERE messages.id = ?
UNION ALL
SELECT messages.id FROM Messages
INNER JOIN AllMsgs parent
ON messages.parent = parent.id )
SELECT {msg.*}, {u.*}
FROM Messages msg JOIN Users u on msg.Author = u.id
WHERE msg.id IN ( SELECT id FROM AllMsgs )
", 43);
msgs.AddSqlReturnDefinition(typeof(Message),"msg");
msgs.AddSqlReturnDefinition(typeof(User), "u");
object[][] messages_and_users = msgs.Execute();
Here is how you use this now:
foreach (object[] message_and_user in messages_and_users)
{
Message message = (Message) message_and_user[0];
Console.WriteLine("{0} - {1}", message.Author.Name, message.Id);
}
Notice that even though we brought the user from the database, we don't access it from the tuple, we access it normally, from the message object. NHibernate is smart enough to figure out that it already loaded it, and can use it directly.
A note, NHibernate itself can use the mapping file to hide all of this from you, which you can later modify without touching the code (or even recompiling). Active Record has no such facility that I know of, though.
This post is dedicated to the localhost blogger.
+972 52-548-6969
