CODEWITHSUNDEEP
entity-frameworkframeworksinsertentity
PaulMcCaffery
PaulMcCaffery

Reputation: 145

Entity Framework and Identity Insert

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.

Upvotes: 3

Views: 2931

Answers (1)

PaulMcCaffery
PaulMcCaffery

Reputation: 145

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.

Upvotes: 5

Related Questions