Generate non-identity primary key

My workplace doesn't use identity columns or GUIDs for primary keys. Instead, we retrieve "next IDs" from a table as needed, and increment the value for each insert.

Unfortunatly for me, LINQ-TO-SQL appears to be optimized around using identity columns. So I need to query and update the "NextId" table whenever I perform an insert. For simplicity, I do this during the creation of the new object:

var db = new DataContext( "...connection string..." );
var car = Car
{
    Id = GetNextId<Car>( db ),
    TopSpeed = 88.0
};
db.InsertOnSubmit( car );
db.SubmitChanges();

The GetNextId method is something like this:

public int GetNextId<T>( DataContext db )
{
    using ( var transaction = new TransactionScope ( TransactionScopeOption.RequiresNew ) )
    {
        var nextId = (from n in db.GetTable<NextId> ()
                      where n.TableName == typeof(T).Name
                      select n).Single ();
        nextId.Value += 1;
        db.SubmitChanges ();
        transaction.Complete ();
        return nextId.Value - 1;
    }
}

Since all operations between creation of the data context and the call to SubmitChanges are part of one transaction, do I need to create a separate data context for retrieving next IDs? Each time I need an ID, I need to query and update a table inside a transaction to prevent multiple apps from grabbing the same value. The call to SubmitChanges() in the GetNextId() method would submit all previous operations, which I don't want to do.

Is a separate data context the only way, or is there something better I could try?

ansaurus

tags:

views:

answers:

Generate non-identity primary key

related questions