NQG will now automatically create the NamedExpression and ManyToOneNamedExpression files, in a language of your choice, so you basically point it at a directory, let it run, and then just include all the files in that directory.

You can get it here.

Update: I fixed the warning in the generated files, and gave it a name without spaces, so it wouldn't break on download.

Like I said in my previous post, this is going to be all commentary. A series of compeltely unplanned actions on my part brought about an interesting result. Take a look at the following query:

Give me a comment in this post that also has a "Active Record Rocks!" as its content.

Let us try to do it a little harder, give me a post in any of the following my blog or yours, whose title is either "Operator" or "Overloading":

And in VB.Net...

Where it is even more natural (try to read it out load).

Note: This require that you would get the new NamedExpression and the ManyToOneNamedExpression files, since they contained the operator overloading stuff.

P.S: I file this post under the Linq category, do you think it deserve it?

This time, I am going to try to do it without commentry. The next post is where all the fun will begin.

NHibernate Query Generator has been updated so it could work with Active Record as well. The way it works is simple, point it at an Active Record assembly, and it will spit out the generated query files. Usage:

It supports generating C# or VB.Net code. I recommend adding this as a post-bulid step to your project:

This command will scan the generated assembly and generate the query files into the Queries directory. The unfortunate side affect means that after modifying your classes, you would need to recompile in order to get the new queries in place, but that is not a big hurdle, as far as I can see.

You can get the new version here (grab the 1.1 MSI). One word of advice, you need the NamedExpression and the ManyToOneNamedExpression files (since the generated code need them), which you can get at the respecive links. You would really want to do this, because they contain a very important update that I'll talk about in the next post...

Have fun...

I have been head down in NHibernate for the last several months, and as a result, I think that I started to miss just how enabling Active Record really is. This weekend I have been working on with it with a venegance, and I love it. The NHibernate 1.2 integration is really important, because it allows to infer even more stuff!

Allow me to present, in all its glory, the amount of stuff needed to make a class persistent:

The collapsed properties merely hide the get/set clutter, by the way.

This is enough information for Active Record (along with the other classes defined in the same way, to allow me to work against the model immediately after creating it!

Note, this image was generate using Active Writer and represnt a domain model similar to a project I am currently working on. I spared you the prolifieration of DateTime in the model and merely put it in three or four places just to show what it was in general.

(continued below)

Now, this model is not showing unrelevant additonal entities (and entities attributes) but it is complete enough that you would understand the general theme of things. The requirement is finding all potential employees in the roganization for a specific vacant position.

Finding an employee consists of (with an implict "as of DD/MM/YYYY date" appended to each statement).

• Finding all the employees that are in the Position's Location at those date ranges.
• For each of those, verify that there isn't any rule broken if I position them there, discard those that break rules with high severity.
• For each of the remaining ones, display how many other employees in each of the employee's group are positioned in the same department, per each group the employee belongs to.

A typical output should be something like:

For poistion Manager in Mainframe Division:

• John Brane - Everyone: 9, MainFrame Guys - 3, Managers - 0
• Mariah G. - Everyone: 9, Managers - 0, Has MBAs - 2

Rules, as you can see in the diagram, are attached to pratcially everything, and they are inheritable to the N-th degree.

So, in order to verify that an employee positioned in a certain Position, I need to gather the following rules:

• Emloyee's current rules
• > All of the Groups the employee belongs to rules
• > All the location's rules
• > All the department (and all their hierarcies) rules
• Strip duplicate rules, using first come win approach. {duplicate rule is indicated by a rule type enum field}

That is per employee. And then I need actually run the rules, and a rule may need additional data (employee's other positions, past history, etc). Oh, and this really better be done on the fly. This is the issue that I am talking about here, which used to take positive infinity or more to run.

At the moment it is 01:17 AM here, so I'm going to leave the question open. How are you going to handle this case? Assuming that everything is mapped using NHibernate and you got full freedom to handle this any way you want. I'm going to see if anyone can come up with an interesting suggestion before I post how I handled this situation tomorrow.

In my previous post about NHibernate and Stored Procedures, I showed how it can be done, and I closed of with this:

Galen commented:

I won't get into the issues I have with "SP for everything", I already expressed them (at some length), elsewhere.  NHibernate is a great tool for abstracting the database, and calling Stored Procedures is a good step forward, mainly because some issues are best solved with them.

The main problem with stored procedures is that they rob NHibernate one of its most important advantages, its flexibility. If you want to do an arbitrary join between three entities and sort by a forth, you can do it very easily. This opens up a lot of options that you just can't have with SP. Using SP, if the SP doesn't allow it, you just can't do it. And any time you have a new need you have to either create a new SP or add parameters, and I am not sure which is worse.

Here is what I would recommend, assuming that the "Give Me Stored Procedures Or Give Away The Database" mindset is prevasive. Do all selects from views (preferably thin views), and all CUD via stored procedures. I had quite a success with using views for NHibernate, and I don't really care how NHibernate is writing stuff to the database. That is going to always be fairly simple insert/update/delete statement, and as long as the SP keeps the usual semantics, everything is going to be fine.

In most cases, this makes the DBAs happy, since then they get to deny everyone direct access to the databases, and they control both the views and the SP. However, this also means that NHibernate is mostly free to join between the views, so it doesn't lose any of its power.

Take into account that writing the SP and wiring NHibernate to use them is still not simple, but you get all the power of NHibernate and the DBAs are happy because they get to keep all of the control in their hand and can optimize the database to their heart's content.

Now, if you can't get your data architects to accept that, it is a problem.

Would I use NHibernate is a situation where SP are really the only way to use the database? Yes. But you should read this statement with the qualifier that says "I really like NHibernate and I know what I'm doing there." There is a lot of value that NHibernate brings to the table quite outside from the flexiblity of working with the data model (just the Unit Of Work is a huge issue for me, and I could gush on and on and on about it... hm, actually, I do ). But, I don't have any experiance in working with NHibernate in a SP-only manner. (And if I have any say in the matter, I won't :-) ). It might be better to try asking the Java guys that has done it before.

I would highly recommend against rolling your own, for reasons already mentioned.

I have a piece of code that has to calculate some pretty hefty stuff over a large amount of data. Unfortantely, that large amount of data took large amount of time to load. By large amount I mean, I walked away and had time for a coffee, chit chat, about three phone calls and a relaxing bout of head banging, and it still continued to pry into the database, and likely would continue to do so until the end of time or there about.

This calculation has two main charactaristics:

1. It is vital to several core functions of the system.
2. It is very highly preferred to make this calculation on the fly. Doing it on the backend is possible, but will cause a lot of major complications.

So, in the middle of checking the price of a dozen new servers (and a safe place in Nigeria, once the client hear about this), it occur to me that while pre-mature optimization is evil, maybe optimization itself has some value and that Nigeria might have to wait for another day.

After carefully thinking about the scentific process (i.e: observing the occurances, forming a theory, preparing experiments, proving a theory, arguing for a decade about what it means, etc...) I decided to take a more direct approach and looked at what I was trying to do.

Then I added this to the query:

And I re-run the whole thing. The performance benefit was four orders of magnitude. And by that I mean that the page is still very heavy DB wise (around ~50 queries on empty cache, which is my usual benchmark), it actually complete in real time, and all the rest of that stuff are things that are very easy to solve (grab those three pieces of code in one shot, instead of five, etc).

Of course, I then had to spend about half an hour staring at the generated query and think about what it was doing (there was a CROSS JOIN there that scared me) before coming to the conclution that it really was a good to fetch all that data. Well, almost. There should have been around ~6000 rows returned from this query, but only 2 were returned.

After a long bout of head scratching, I determained that the fault was at my mapping. I had several places where I had where clauses like this one:

I'm pretty sure that you can see where this is going. In a left join scenario, this (non-nullable) column is going to be null, so it would evaluate to false, making the join into an inner join, reducing the returned data by quite a bit. I'm writing this post as I go along fixing this issue. Right now the situation is not much improved :-(

After a long and hard battle, I managed to drop it merely three order of magnitude down, and I run into some issues with the code that uses it, so I need to fix those first.

I'll try to post some ideas about how to solve the complex SELECT N+(M*N*Zm)+1 issues (where N is the number of items, M is the number of collections in each item, and Zm is the number of items in each collection in each item). (The short version, don't do this.)

I branched Active Record to move it to NHibernate 1.2, because of some needed features from NHibernate 1.2 that simply do not exist in NHibernate 1.0.2. The branch at the moment should handle generics natively and easily, so you should be able to do this:

And Active Record would figure out all the rest. (Although it is probably recommended to give it at least the table / column for the relation).

But, as much as I like features that include "someone else will figure it out", the nicest feature of all is this one:

This one involves taking advantage on NHibernate 1.2 projections, and it makes certain types of action possible, the above query can also take an ICriteria, which make it a very easy way to handle what previously required HQL to do. There are a large number of builtin projections that can be used, and I found uses for them about 5 minutes after seeing them in action.

Often, when introducing NHibernate, I need to integrate with existing database and infrastructures. Leaving aside the question of stored procedures (since I already expanded on that in length here), which are avialable on NHibernate 1.2, I want to focus on using SQL Functions here.

(One again, I'm back to the Blog -> Posts -> Comments model)

Now, there are four types of SQL Functions that you might want to use:

• Scalar functions that are a part of an entity.
  A post's total  spam score may be calculated using a SQL Function, and it is part of the properties of the object.
• Scalar functions that are used for calculations, and should be called explicitly.
  A blog's popularity score may be calculated using a SQL Function, but it is too expensive to calculate and not often needed.
  Note: Only this requires NHibernate 1.2, all other features can be done using NHibernate 1.0.2
• Table valued functions (or stored procedure, for that matter, but that is a bit harder) that return entities:
  A selection of posts with specific spam score it one example.
• Scalar functions that you want to use as part of your HQL queries.
  For instnace, you may want to use database (or user defined) functions as part of your HQL queries. Think lower(), dbo.postSpamScore(), etc.

Let us attack each of those in turn, shall we?

First, we have a scalar function that is a property of the entity, in this instance a post' spam score. The SQL Function is defined so:

Not very exciting, I know, but for our purposes, it is enough. Now, I need to define the following in the mapping file:

The formula attribute is very powerful, you can even put SQL statements that will be executed as corelated sub queries (if you database supports it).

That is all you need to do to get the value from the function into your entities. In fact, you can now even perform HQL queries against this property, like this:

Second case, we want to get a scalar result from the database, and we are not interested in using ADO.Net directly to do so. Note that this is only possible with NHibernate 1.2, in NHibernate 1.0.2, you will need to use ADO.Net calls directly. Again, the SQL Function is very simple (for demonstration only, you can do anything you want in the SQL function, of course)

Now, let us map this query so it would be easy to use...

And the code that uses this:

A couple of points here. Notice that we are using the NHibernate notations for parameters in the query (:blog), and that we are passing an object to the query, not the identifier. I find this style of coding far more natural and OO than the equivalent ADO.Net code. For that matter, the equivalent ADO.Net code goes on for half a page or so... :-)

Third case, using a table valued function. Table valued function obviously cannot be used as a property of the entity. (To be exact, they can be used as a collection, but this is advanced functionality, and it is only avialable in NHibernate 1.2, so I'm not not going to cover it here). Fairly often, you want to use this for getting a collection of entities from the database using logic that reside in the database (for performance or historic reasons).

Here is my demo function:

Now, let us map this for a set of entities:

Notice that I map all the colums to their respective properties (I could also use {post.*} if I wanted all of the columns without bother with specifying each and every one). And that I can pass parameters easily to the query.

Now, I can just query them like this:

Against this is much more readable than the equivalent ADO.Net code, and I get full fledged objects back. If you want to do projections (show a post summary, etc. Using just part of the entity) you need to map the resulting projection as if it was an entity, NHibernate doesn't support SQL projections. (In practice, this is not an issue).

The last case is using user defined SQL Functions (or bulitin ones that NHibernate doesn't recognize by default) in your HQL queries. In order to do this, you need to either extend the dialect for your database, or dynamically add new functions to the dialect in the session factory (not really recommended). 

Let us assume that you really want the ISOweek function. (See here for the implementation, if you really care). The function declaration is:

I would recommend extending the dialect for the database with the new function, like this:

A couple of things to note here. If this is a user defined function, you have to add the schema (in this case, dbo). The function name must be in lower case (HQL is case insenstive, so you can use whatever case you like in the queries, but you register the function with lower case). You then configure NHibernate to your MyDialect instead of your database dialect, and that is it. You can now issue HQL queries like this:

Note that you must still use the "dbo." in the HQL.  Again, you can pass arguments naturally:

(Not that I can't think of a reason why you would want to excute the last two queries).

So, this is just about all you would ever want to know about SQL Functions, and if you kept reading up to now, you are really passionate about it.

Happy (N)Hibernating... :-)

If you are using NHibernate.Generics with NHibernate 1.2, you may run into casting issues, this is because NHibernate.Generics and NHibernate 1.2 both tries to make the collections generic.

You can either stop using NHibernate.Generics (their main purpose, allowing generic collections in NHibernate) is no longer needed. But their secondary purpose, automatic syncronization between related properties is very nice in many scenarios. Until I'll get around to porting them to 1.2, you need to specify generic="false" in the mapping of all collections that uses NHibernate.Generics for them to work.