How to mimic the use of sequences (from Oracle) using SQL Server and Entity Framework

Question

We have always enjoyed the use of a sequence in Oracle databases in order to create globally-unique primary key IDs across an entire database. So much, that we will mimic the same thing when using SQL Server databases:

CREATE TABLE MainSequence(
    Id int IDENTITY(1,1) CONSTRAINT pkMainSequence PRIMARY KEY
)

I'm trying to switch over to Entity Framework, which is very new to me. It seemed like it would be trivial to create an extension method that I could use to quickly get the next available globally-unique Id.

public static int GetNextId( this Entities db ) {
    var ms = new MainSequence();
    db.MainSequences.AddObject( ms );
    db.SaveChanges( SaveOptions.None );
    return ms.Id;
}

Since it's an identity column, all I should have to do is add a new object to the database and save the changes so that the Id property is updated with a real value. This works fine. But I seem to run into trouble when trying to use it for foreign-key-related tables:

var dataId = db.GetNextId();
db.Datas.AddObject( Data.CreateData( dataId, someValueForThisColumn );
db.Caches.AddObject( 
                    Cache.CreateCache( db.GetNextId(),
                    DatabaseMethods.GetOrAddLocation( source.GetLocationText() ),
                    DateTime.Now,
                    dataId ) );

The Cache table has a foreign key to the Data table. When SaveChanges(); is called immediately after this, an exception is generated.

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'pkData'. Cannot insert duplicate key in object 'dbo.Data'. The duplicate key value is (78162). The statement has been terminated.

For some reason it appears the new data row is trying to get inserted into the database twice, though I'm not sure why that would be. I've confirmed that for every time the code is run, a different MainSequence ID is returned. It seems as though calling db.SaveChanges whenever a new ID is obtained is the problem, but there's no other way that can get populated with a real int and I don't see why it would be a problem. What am I doing wrong?

Either wait for SQL Server 2012 (which will have full support for the SEQUENCE db object), or then use the IDENTITY column specifier to let SQL Server automatically dish out sequential ID's for your table - don't replicate this manually, that's a disaster waiting to happen.... — marc_s
– marc_s, Commented Feb 19, 2012 at 16:57
We've been replicating the use of Sequences with SQL Server for years without a problem not using Entity Framework. Despite the title of my question, I don't really believe the problem has anything to do with replicating a Sequence. It seems like the problem I'm having is a misunderstanding of the SaveChanges(SaveOption) method where either I'm not allowed to call it several times, or need to do it in some other way. — Sam Rueby
– Sam Rueby, Commented Feb 19, 2012 at 17:18
Does it work correctly if you try to test it with a fixed value assigned to dataId (var dataId = 12345) — Wiktor Zychla
– Wiktor Zychla, Commented Feb 19, 2012 at 17:35
What do you enjoy about them - the fact they're globally unique? If that's it why not generate sequential guids? — James Gaunt
– James Gaunt, Commented Feb 19, 2012 at 18:15
@Sam.Rueby on the other hand, I don't think it's wise to always adopt the opinion that the way you're trying to do things is the right way. I'm not saying for this case, but in general if something is hard to do there might be a reason - and an easier solution doesn't make it inferior, especially if it still accomplishes all of your goals. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 19, 2012 at 19:58

Sam Rueby · Accepted Answer · 2012-02-20 12:48:21Z

1

My problem is the line db.SaveChanges( SaveOptions.None );. My reason for using this option was to avoid SaveOptions.AcceptAllChangesAfterSave, which in my mind, through information gathered from other StackOverflow questions (either invalid or misunderstood) I thought would provide me a basic form of database transaction. I did read the MSDN documentation several times:

After changes are saved, the AcceptAllChangesAfterSave() method is called, which resets change tracking in the ObjectStateManager.

That to me sounds an awful like transaction-change-tracking. I thought that if the changes were not accepted on a save call that the database "transaction" would be rolled back in the case of an error (I later found out this wasn't the case). Instead, all this does is make the ObjectContext still consider the change to be unsaved. So upon the next save call, the "unsaved" changes would be re-applied. Ta-da, primary key exception.This also explains why my main sequence was skipping values.

answered Feb 20, 2012 at 12:48

Sam Rueby

6,2127 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ntziolis Over a year ago

yes indeed, try to stay away from passing around the context, create it, make your changes and then fire ONE save changes call. As soon as you do something that leads you to thinking about manipulating the change state of the entities you're asking for trouble

Collectives™ on Stack Overflow

How to mimic the use of sequences (from Oracle) using SQL Server and Entity Framework

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related