Hi,
We're looking at porting an existing VB6 application to .Net using NHibernate. I've looked through most of it and it seems pretty straightforward, but I'm not sure what to do about the primary keys. This is an SQL 2000/2005 system, and we can't make significant schema changes.

The problem is that the system uses merge replication to sync the data between multiple sites, and the primary keys are implemented as a pair of columns. One column is a site specific char(2), which is no problem, but the other is an identity column. From readings the NHibernate reference, I'm not sure if this can be supported - it mentions that you can't use IIdentifierGenerator to generate the keys, but instead must assign them within the application. Can anyone suggest how we should handle this?

Kev

Sorry to respon to my own post, but aftering a bit of searching the forums I thought I might need to expand on it. Our Identity columns are unique per site, but not within the database. For example, if site 1 and site 2 both add a record to an empty table at the same time, both rows will have the identity value 1. This would obviously generate a conflict when the rows are merged, so the primary key is defined as consisting of a site prefix and the ID. The above example would now generate keys like ('AA',1) and ('BB',1) so there would be no conflict.

Kev

This is a bizarre scenario that I doubt NH would support out of the box, nor would any ORM solution, but NH is probably your best bet.

You might be able to get this to work by writing a custom type and a corresponding implementation of IIdentifierGenerator.

Check out: http://www.hibernate.org/hib_docs/nhibe ... pes-custom

It seems that Linq for SQL supports it - see the example here: http://forum.hibernate.org/viewtopic.php?t=983345. As for bizarre, the design was constrained by the need for replication. I think the original design was done with the betas of SQL 7, when there was no support for identity ranges or any of the other goodies later versions of merge replication introduced. Other options might have been different seed/stepping ranges for the identities on each site, but the team chose this approach, and to be fair it works well



Have you got any pointers to how I might go about this? It seems promising, but my knowlege of the inner workings of NHibernate isn't up to it at the moment. I've taken a look at IdentityGenerator.cs in the sources, and at some examples of ICompositeUserType, but I'm not really sure where to start in terms of putting them together. Are there any examples of this sort of thing out there?

Very interesting. Sorry about the "bizarre" - no offense intended.

Anyway...I'm not positive it's feasible, but I was thinking that you could start with the DoubleStringType in NHibernate.DomainModel and change one of the columns to a string.

However, I just took a stroll through the NH source, and I'm now unsure of my suggestion, and unfortunately I don't have the time to try to flesh it out.

Here's what I've learned.

The identity stuff is coded right into the EntityPersisters. I don't see a good way to indicate to NH that one of your Identifier columns is an identity column that should be excluded from the INSERT while the other one should be included.

The more I think about it, the only issue should be inserting, right? You could implement a component as a composite ID as described at:
http://www.hibernate.org/hib_docs/nhibe ... ompositeid

With that you should be able to load, query, etc.

The sticky bit is inserting. For that, you might need to subclass SingleTableEntityPersister for that particular class. I have a feeling you might be able to solve your problem by overriding GenerateInsertString, but that's just a guess.

None taken - just thought I'd explain why it was implemented that way




I know what you mean. I've just done my own build to have a play, and I'm struggling go see how I could tie in the extra column.


That's what I'm thinking - for the rest of it it's just a regular composite-id, so relationships, querying, etc should just work if I can nail the inserts. I'll take a look at SingleTableEntityPersister and GenerateInsertString (thanks for the pointers) and see what I can come up with

Kev

I know this isn't really relevant to the problem at hand, but this is for my own curiosity. Why didn't you choose "uniqueidentifier" for your primary keys? That's what I've always done when we needed to worry about replication.

I am curious if you find a solution to this problem though. I'll keep watching this forum.

I didn't do the initial design so I can't really comment, but I know it was also necessary to identify the site that raised each record, so this kind of killed two birds with one stone.

These days we use identity ranges for this sort of thing.

Bit of a progress update. I'm getting there, more or less. The solution which is mostly working so far is to subclass the EntityPersister, as suggested by wfassett. It's been a bit more involved than simply overriding GenerateInsertString though. Most of my time has been spent stepping through the NHibernate code observing how things work. Here's what I've had to do so far, anyway:

# Override IdentifierGenerator, to return an IdentityGenerator instead
# Override IsIdentifierAssignedByInsert to return true
# Override GetIdentifier, to check for a zero Id within the key, and replace the returned key with null. This allows the persister's IsUnsaved to work
# Override GetGeneratedIdentity, since otherwise the code below was hard wired to expect a single column identity
# Provide AddIdentitySelectToInsert method, to modify the SQL passed to the database to return the ID column and the prefix column. This was due to clashes between the number of columns for an identity property being hard wired to one, and the code to pull back a created key expecting the returned columns to match the key's composite type

I had to modify the base Persister to allow GetGeneratedIdentity to be overriden. I also had to modify it so the Insert method called an overridable function AddIdentitySelectToInsert, as otherwise it went straight to the dialect without any opportunity to intercept. I'd like to avoid making changes like this, for obvious reasons, but so far I think I might be stuck with them.

I'm now able to save and load entities without any children, but i'm getting errors when I try to persist an entity with children. About to look into that now, but I thought I'd post this update whilst I was coming up for air.

I'm vaguely concerned that I might be setting myself up to hit obscure bugs once we try to implement our actual project, but the choice at the moment seems to be to work this out, or to use LINQ for sql classes. Since I already hit a fairly big problem in the little test project I tried to use with LINQ (nothing to do with composite IDs), I'm hoping I can get this working

Kev

The problem with children is now cleared up. It had nothing to do with my changes, but was due to a silly mistake in my test code. So, on the face of it everything seems to be working. Next step is to come up with as many test cases as I can think of and put it through its paces

Kev