We are currently developing an SDK for our system and evaluating the possibility of having an ORM solution take care of all the mapping between our objects and our database. However, there are a few peculiarities we would like to bring up, hoping that the matter experts will offer some advise. The feedback we are seeking is "there's no way you can do that" or "there is out-of-the-box functionality implemented specifically for this called..." or "it's not easy, but you could find a work around using..."

Retrieve and store ID
When saving new objects to tables that have a primary consisting of one field which also has identity, the value inserted in this field must be retrieved and assigned to a specific property of the object that's being saved (the ID property). I can only guess this is standard behavior in almost any ORM solution, but wanted to mention it.

One object, several tables
There isn't a one-to-one relationship between classes and tables. For the most part, this does not happen, but there are cases in which properties of one class may be saved to more than one table.

Audit trail
When persisting an object, it is sometimes necessary to write to an audit/history table with the primary key of the table that holds the actual object data, the current date, user and sometimes other data.

Property vs. Property.ID
When a property of the object being persisted is another custom class, it is sometimes necessary to submit the ID property of this class instead of the actual class. For example, say we have a class named Department which has an ID int property. Say we also have a class named Employee with two properties: an ID int property and a Department property of type Department. When saving the employee data, the table will require Department ID and Employee ID.

sp_prepexec
Because of our database configuration, our applications cannot run INSERT, UPDATE or DELETE statements directly against it. The only alternative we have come up with is to use the sp_sqlexec system stored procedure (or, alternatively, sp_prepexec or sp_prepare and sp_execute) to execute the ad hoc SQL statements created by the ORM by passing the generated query as a string parameter, meaning we should be able to retrieve it and stop the ORM from executing it itself. Is there a way to do this? Can you think of a better solution?

Versions and compatibility
The SDK we are building is for an application we publish. This application gets upgraded to new versions and some of the changes affect the database. Our SDK needs to support all versions of this application. However, we are not planning on touching our object model. What may happen is that property A which used to be saved in table T, field F in previous versions, may need to be saved to a different field and, potentially, a different table, or not saved at all. We have seen this property-field mapping is oftentimes done via property attributes. Is there a way to add several values of this attribute per property and somehow have a logic that picks the right one?

We are not asking for actual solutions in either of these cases, as we understand you don't have all the details of our application and SDK, but any feedback you offer on this may give us an indication of where to look in the documentation, what type of examples to search for and download and what to start playing with if we decide to create a proof of concept project.

Thank you very much in advance!

Yes. Very easy. The element in the hbm.xml file that determines this is the <generator> sub element of the <id> element. It also works against sequences or other key generation approaches. If one doesn't suit, you can create your own.



If you are talking about fine grained objects, NHibernate encourages you to use a fine grained object model. Eg you may have a ContactInfo class used in say a Person and Company class. The underlying tables would have common columns. In the hbm, you'd use the <component> element to help manage the translation from your fine grained object model to the course grained relational model (if that's how you choose to represent the data). If you are talking about polymorphic relationships, NH also covers this situation.



Yes.. with a bit of once off framwork area effort. Check out the NHibernate.IInterceptor interface.




In NHibernate you submit the entire in memory object graph to the NHibernate Session's SaveOrUpdate method. You don't have to worry about any explicit manipulation of IDs yourself. NH will work out what is new (and insert it and give back the DB key value) what has changed (and update it) without need for any special dirty checking on your part in the domain model. The only "constraint" on the domain design is all methods and properties must be virtual so that NH can create proxies for you.



I'm pretty sure (although someone can correct me if I'm wrong) this is either gonna be a deal breaker for you or will be a bit of ongoing work on your part (eg every time you add a new table the extra work around will have to be applied... kinda defeats the purpose of the ORM). NH generates ADO.Net SQL statements with bind variables ready to go for you by default.



I think it depends on how far ranging your changes are. But having one value type property (eg a string property) on a class to be stored in a completely different table to the rest of the value types in the same class is not going to be easy. On the other hand if you just don't need the column, removing it from the class (and hbm) is no problem.

I've used NHibernate for the last 3 years. Most recently on a project using around 140 tables and a domain model that is fine grained and also has polymorphic relationship mappings. I would recommend using NHibernate.

First of all, thank you for your comprehensive response. I'm glad to read about all that's natively supported.



I'm not quite sure we're talking about the same thing. Say you have class X with properties A, B, C and D. Class X should be stored in the database in two tables: X1 (A, B) and X2 (C, D). Is support for this native in NHibernate.



That would blow. Now, you mention that NHibernate generates statements. Is the end result a SQL statement I could have access to and execute directly against the database myself? If that's the case, all I need to do is call the sp_executesql sproc and pass this statement as its only parameter. I know... Sounds quite dirty, but it may be our only solution.



This may be a problem too. The changes are never huge, but it may happen that in one version you store a property in table1, field1 and in the next version you store it in table2, field2. This is rare indeed, but it does happen and we need to be able to account for these cases, even if it's not a straightforward solution, since this would be a one-off case.

Thank you again.

I think we're talking about the opposite thing here. I can't think of an example where you would have more tables used to represent your data model than classes to represent the equivalent domain model apart from a polymorphic relationship where it's common to have a table per sub class so the data accessible to your subclass is in effect mapped to more than one table. I'll give you a more concrete example of what I'm talking about regarding a fine grained domain model and maybe you can do the same...






Looks like it's new to 1.2... http://www.hibernate.org/343.html. The doco is at http://www.hibernate.org/hib_docs/nhibe ... erysql-cud

I've never used it so don't know how fit for purpose it is for you.



You may have constraints beyond your control here, but my opinion on this is the database schema should not be some kind of island immune from effects of refactoring. Refactoring the DB is (again this is just my opinion) part of the cost of refactoring. If you change a property name in a class I believe the corresponding column name should change. Not keeping the DB in sync puts you on the way to having to maintain a system that is difficult to understand (at least for those who have to work with the DB or get reporting data out of it).