views:

29

answers:

1

Object A has a one-to-many association: many object B.

When I look in the database -- TableB -- I'd like to see the unique, readable string A.Name instead of having always to join or subselect on a surrogate integer identifier to see the name.

I can map Name as the identifier for A, but this causes lots of extra SELECT queries because NHibernate can't identify whether an instance of A is transient or persistent.

I imagine that I could use a composite key, combining a native-assigned surrogate key with the natural key. This seems sub-optimal, but I'd love to hear some opinions.

What I'm really looking for is a strategy for using a single-column natural key while allowing NHibernate to identify transient instances.

  • Is it possible?
  • What is the mapping -- fluent or hbm?

On the other hand, if this is all a terrible idea and I should just rely on database views with subselects, please explain.

Thanks.

+1  A: 

It's a terrible idea and you should create a view that does the join for those occasions when you need to "look in the database".

It's a terrible idea because Name isn't the primary key of table A.

But I think you can do it if you put a unique constraint on A.Name and map it as the identifier. I'm not sure if NHibernate defaults to null or empty string for the unsaved value but you can specify it using

Id(x => x.Name).GeneratedBy.Assigned().UnsavedValue(string.Empty); // or null

If you use string.Empty than A's constructor must initialize Name to string.Empty. I would also remove the surrogate identifier from A and maybe the table itself since it serves no purpose.

Jamie Ide
Terrible idea -- fine. Why?
Jay
Sorry for the short answer, I've expanded it.
Jamie Ide
Thanks for the update. The problem with using `UnsavedValue()` is that I would never be able to persist it-- as soon as I assign a name, NHibernate would treat it as already saved, no?
Jay
I haven't worked with assigned IDs, but I think the workflow is to create the object, call ISession.Save() on it, set the ID, then flush the session. NH will issue insert then update; the downside is that the ID has to be nullable for the insert to succeed. You also might be able to use a version field, see http://stackoverflow.com/questions/1070311/how-to-save-a-child-with-assigned-id-in-nhibernate
Jamie Ide