CODEWITHSUNDEEP
sql-serverentity-framework
Manish
Manish

Reputation: 1776

SET IDENTITY_INSERT not working

Are there any circumstances when SET IDENTITY_INSERT will not work if executed using entity framework and SQL Server, though it will work from SSMS on the same database using the same credentials (sa)

System.Data.Entity.Core.Objects.ObjectContext oContext = ((IObjectContextAdapter)dbContext).ObjectContext;

ExecuteSql(oContext, "SET IDENTITY_INSERT dbo.Albums ON");
ExecuteSql(oContext, "INSERT INTO dbo.Albums ([Id], [Name], [Description]) VALUES (-2, 'album1', 'album1')");

This throws an error:

System.Data.SqlClient.SqlException: 'Cannot insert explicit value for identity column in table 'Albums' when IDENTITY_INSERT is set to OFF.

However, when I run these same 2 lines in SSMS, the identity insert works fine.

Using:

The ExecuteSQL method is as follows:

public static void ExecuteSql(System.Data.Entity.Core.Objects.ObjectContext c, string sql)
{
     var entityConnection = (System.Data.Entity.Core.EntityClient.EntityConnection)c.Connection;

     DbConnection conn = entityConnection.StoreConnection;
     ConnectionState initialState = conn.State;

     using (DbCommand cmd = conn.CreateCommand())
     {
         cmd.CommandText = sql;
         cmd.ExecuteNonQuery();
     }
}

Upvotes: 0

Views: 3808

Answers (1)

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

Try Putting SET IDENTITY_INSERT TableName ON in the same Query, as next:-

ExecuteSql(oContext, "SET IDENTITY_INSERT dbo.Albums ON; 
INSERT INTO dbo.Albums ([Id], [Name], [Description]) 
VALUES (-2, 'album1', 'album1')");

Upvotes: 3

Related Questions