ExecuteSqlCommand and connection state RRS feed

  • Question

  • It seems that ExecuteSqlCommand closes the connection after it completes, no matter if I manually opened the connection. To my opinion It would be logical to leave the connection as opened, if it was opened prior to ExecuteSqlCommand method execution. Is this behavior by design?

    I ask this because I am trying to import some data from another database type to sql server. Main application is using EF CodeFirst (v4.4) approach with Identity columns. That is all working fine. I would like to make some small app that would reuse the EF part (dll), so I would just convert the data, add converted data to DbContext, and save changes. Currently, it works, but with one problem: Identity column is still autogenerating values.


    // get the table name that we need to set IDENTITY_INSERT to ON string tableName = GetDbSetName<T>(); // set IDENTITY_INSERT to ON so we can update autoincrement column int result = context.Database.ExecuteSqlCommand(string.Format("SET IDENTITY_INSERT {0} ON", tableName)); // save changes int insertedRecords = context.SaveChanges(); // set IDENTITY_INSERT to OFF (default) result = context.Database.ExecuteSqlCommand(string.Format("SET IDENTITY_INSERT {0} OFF", tableName)); // close connection context.Database.Connection.Close();

    As you can see, I am manually opening connection, in hope that it will stain opened, since IDENTITY_INSERT remains ON while the connection is opened. SaveChanges  call will be updating only the entities of type T, which means that only the table thar I am setting IDENTITY_INSERT will be updated.

    What can I do to overcome ExecuteSqlCommand problem?

    Thanks in advance.

    Monday, August 27, 2012 7:45 PM


All replies

  • Hi Goran_,

    Welcome to the MSDN forum.

    You can try this to maintain the connection open:

                using (var context = new UnicornsContext())
                    Int32 insertedRecords = context.SaveChanges();

    Also, this article is helpful for you I think: http://blogs.msdn.com/b/diego/archive/2012/01/26/exception-from-dbcontext-api-entityconnection-can-only-be-constructed-with-a-closed-dbconnection.aspx

    Good day.

    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Proposed as answer by Doraemon_3 Thursday, August 30, 2012 9:49 AM
    • Marked as answer by Goran _ Thursday, August 30, 2012 11:17 AM
    Wednesday, August 29, 2012 8:14 AM
  • Hi Alexander,

    thanks for the info and help. I have tried what you suggested and it worked only partially. I am doing a successive table data copy, one table after another. For first table everything works ok. However when second table needs to be saved, on SubmitChanges method I get following exception:

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

    Now before explaining what I have tried, I need to say this: I am sure that I have values in identity field for each entity, since I have two versions of software: one that is saving converted data using the ADO.NET SqlCommand, and another using EF. The code to read data and convert it is the same in both versions. When I use SqlCommand, everything works fine, so Identity value is supplied. Property "Not for replication" on identity field is set to false. After saying that, here is what I have tried:

    private int SaveWithContext<T>(IList<T> list) where T : class
        var context = new OeeCoachEntities();
        foreach (var item in list)
        var objectContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)context).ObjectContext;
        objectContext.ExecuteStoreCommand(string.Format("SET IDENTITY_INSERT {0} ON", GetTableName<T>));
        int insertedRecords = objectContext.SaveChanges();
        objectContext.ExecuteStoreCommand(string.Format("SET IDENTITY_INSERT {0} OFF", GetTableName<T>)); 
        objectContext.Connection.Close(); return insertedRecords;

    Then I have tried to keep the ObjectContext alive and it's connection open through the duration of converting process, but I still got the same exception. I am not sure if it will be able to notice newly added records Please not also, that this EF 4 CodeFirst model that I am using in this example, is used in main application, and handles CRUD operations without any problems, so it should not be the problem in database schema, nor with model mappings, or similar.

    • Edited by Goran _ Thursday, August 30, 2012 10:34 AM
    Thursday, August 30, 2012 10:27 AM
  • Hi Alexander,I was able to find the cause of the problem: in my mapping files I am not excplicitly setting my PK column to be used as Identity. This is done automatically by EF when generating schema. That all works well.Now, probably EF during the insert does not include PK value, since it is identity column.

    The reason why it worked for first table, is because I have turned it off in model builder few days ago, when struggling with IDENTITY_INSERT problem.

    modelBuilder.Entity<Activity>().Property(p => p.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);I have added one more constructor 

    I have added one more constructor on DbContext with boolean switch to turn off identity for PKs, and now it is working ok.

    Thanks for the help.

    • Edited by Goran _ Thursday, August 30, 2012 11:14 AM
    Thursday, August 30, 2012 11:14 AM