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?

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.