Can NHibernate, Subsonic or L2S do Per-Entity Auto-Increment?

views:

143

answers:

Can NHibernate, Subsonic or L2S do Per-Entity Auto-Increment?

I have a SQL Server 2008 database with a composite key: ProjectID (GUID) and TaskID (int). ProjectID is a foreign key to a Projects table. I want to have TaskID Auto-Increment, but restart for every ProjectID (that is: every projectID should have 1,2,3,... as TaskID).

To my knowledge, this is not possible in SQL Server out of the box, and I'd need a stored procedure. Now before I dive into that, I wonder if I can instead do that on my ORM side? I'm undecided between NHibernate 2.1.2 and Subsonic 3.0, but even Linq-To-SQL is an option (Entity Framework is not) if that is possible with it.

I know I can just manually write that code and I know that almost certainly a "SELECT max(TaskID) FROM Tasks WHERE ProjectID = @projectID" is needed in any case, but If I can avoid doing that and instead have my ORM do that, that would be nice.

I haven't found anything in their respective documentations, but I don't really know if there is a proper term for this scenario?

+1 A:

There's a reason this is hard - and I hate to sound this way but this design is not a good idea. Oren and I actually had this discussion (surrogate keys and why you need to use them) - tying meaning to your PK will hurt you almost always.

Let your indexer be your indexer, let your FK be your FK. Give the PK over to a AUTO INCREMENT and let it do it's job. Simple wins.

I would suggest a trigger (GASP!) or perhaps a separate routine on inserts that you can fire which runs an update for a newly-inserted record.

Or you could use NHib's futures stuff (or SubSonic's batch or CodingHorror) to have it execute all at once.

But please, for the children - composite keys are the devil.

Rob Conery 2009-12-17 10:04:48

You are right in one way (I decided to change the Primary Key from the projects table from GUID to ID. Turned out that instead of changing all tables it was just easier to drop the entire DB and recreate it from a script), But I have to think about that. If the Primary Key is "Just a random extra field with no meaning", I don't see much point in it, as the Unique Constraint has to be a combination of ProjectID and TaskID. But I haven't had large databases yet, so I'm just saying that I don't completely get it :)

Michael Stum 2009-12-17 19:29:24

As for your suggestion: I think that one way or another I have to do that, either as a Trigger, a SProc or in my business logic. Essentially it's "just" a transaction that locks the table for writes and executes a select max() and an insert. Gonna check which is most appropriate.

Michael Stum 2009-12-17 19:31:16

The PK shouldn't have meaning other than identifying the row - so it's actually good that you don't see the point of it. The rest of your DB depends on this very point - that every table has a column that uniquely identifies the row's contents. Constructing that with meaninful data is bad because the *meaning* of the data can change - that means your PK changes.I had this happen with Kona where I used "UserName" as the unique id. Initially I thought that users of the system would need a unique name and it seemed like a good idea.

Rob Conery 2009-12-20 03:13:55

A problem arose, however, when I wanted to allow users to remain anonymous, asigning them a Guid instead. I couldn't do that - userName as my PK was locked in for me as a string and furthermore I'd need to change the PK once an anon user logged in - silliness! I'd have to go update all the related tables just to suit!That's why PK's need to have meaning to the DB, not you.

Rob Conery 2009-12-20 03:15:33

ansaurus

tags:

views:

answers:

Can NHibernate, Subsonic or L2S do Per-Entity Auto-Increment?

related questions