Entity Framework and Identity Insert

Question

I am writing an application that exports data and serializes it to file for archiving old data.

There may be occasions where for some reason select data needs to be re-imported. This has been causing me a problem because of an identity column.

To get around this I am performing the work inside a transaction scope. Setting the Identity Insert On for that table and then updating my transaction e.g.

using (TR.TransactionScope scope = new TR.TransactionScope(TR.TransactionScopeOption.RequiresNew))
                {
                    // allow transaction nbr to be inserted instead of auto generated                        
                    int i = context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Transactions ON");

                    try
                    {
                        // check if it already exists before restoring
                        var matches = context.Transactions.Where(tr => tr.transaction_nbr == t.transaction_nbr);

                        if (matches.Count() == 0)
                        {
                            Transaction original = t;                                
                            context.Transactions.AddObject(original);
                            context.SaveChanges();
                            restoreCount++;

But I receive an exception saying:

Explicit value must be specified for identity column in table either when IDENTITY_INSERT >is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity >column.

I am assuming the entity framework is trying to do some sort of block insert without specifying the columns. Is there anyway to do this in the entity framework.

The object is large and has a number of associated entities that are also deserialized and need inserting so I want to let the entity framework do this if possible as it will save me a lot of extra work.

Any help is appreciated.

I don't know what I have done but it would appear to have rectified itself. — PaulMcCaffery, Sep 10 '13 at 14:27
Either I have tweaked the model somehow to make it possible or there may have been some scoping issues that meant it couldn't re-import because of some hangover from the previous export — PaulMcCaffery, Sep 10 '13 at 14:28
The `StoreGeneratedPattern` of the identity columns in the EF model is `Identity`, so EF does not include it in the insert statement. I think you should use a shadow model with `StoreGeneratedPattern.None` on all identity columns involved. Or find a way to adapt the model at runtime, a bit like [here](http://stackoverflow.com/q/7079797/861716). — Gert Arnold, Sep 10 '13 at 16:10

score 5 · Accepted Answer · answered Sep 11 '13 at 09:02

Gert Arnold - that was the answer to my question. Thank you.

I did read that elsewhere and had set the value in the object browser so thought this was suffice. I also double checked the value by right clicking the .edmx and Open With to see the details in an XML editor as suggested in another post.

When I checked the value in the XML editor initially it too was "None" so assumed this wasn't my problem. But I guess just going in there and saving rectified the problem first time around.

The second time round after I must have updated the model from the database I had to repeat this step upon your suggestion. But the second time round the StoreGeneratorPattern was different to the one set in the object browser so I needed to manually change it in the XML.

In my circumstance this is fine since normally the records are inserted via another mechanism so the identity is just getting in the way for me as the identity will always be inserted as an old (used to exist) identity value which is being temporarily restored.

Thanks for your help.

Or if you're using Code First, add [DatabaseGenerated(DatabaseGeneratedOption.None)] as an attribute of the primary key property. — Roberto Bonini, Sep 27 '14 at 13:22

Entity Framework and Identity Insert

1 Answers1