none
IDENTITY_INSERT and linq

    Question

  • I have been trying to get IDENTITY_INSERT ON to work with linq
    insertOnSubmit with SQLExpress. It seems SQL is ignoring the IDENTITY_INSERT.

    This is for a daily database update from an old database while the migration
    is proceeding. The identity has to match the old data...


    Any Ideas?
    ie.
    db.ExecuteCommand("SET IDENTITY_INSERT cdTEMPERTest ON");
    db.cdTEMPERTests.InsertOnSubmit(newRow);
    db.SubmitChanges();

    Tuesday, October 28, 2008 2:08 AM

Answers

  • There is a property of Column attribute (of System.Data.Linq.Mapping namespace) called IsDbGenerated. When you add this property for a Column and set it to true, it says the DataContext that column will be filled by the database i.e. its value will be generated by the database. This is very useful for Identity Columns. In this case, you also donot required to set the IDENTITY_INSERT on. So, suppose we have an identity column say "UserID", and we want to insert a row in our database table named say "TestTable". The code to do this is as follow

    //In the class TestTable (which is mapped for LINQ)
    [Column (Name="UserID",IsDbGenerated=true,DbType="int Not Null IDENTITY")]
    public int UserID
    {

    get;
    set;
    }

    //In the class where you want to insert row into table TestTable
    TestDBDataContext tdb=new TestDBDataContext(ConnString);
    tdb.TestTable.
    InsertOnSubmit(newRow);
    tdb.
    SubmitChanges();


    In this way you can insert a new row.


    Monday, December 08, 2008 6:15 PM

All replies

  •  danielSkate wrote:
    I have been trying to get IDENTITY_INSERT ON to work with linq
    insertOnSubmit with SQLExpress. It seems SQL is ignoring the IDENTITY_INSERT.

    This is for a daily database update from an old database while the migration
    is proceeding. The identity has to match the old data...


    Any Ideas?
    ie.
    db.ExecuteCommand("SET IDENTITY_INSERT cdTEMPERTest ON");
    db.cdTEMPERTests.InsertOnSubmit(newRow);
    db.SubmitChanges();

     

    Try passing a connection object to the constructor of your data context, e.g.:

     

    Code Snippet

    SqlConnection conn = new SqlConnection(someConnectionString);

    using (DataClasses1DataContext nd = new DataClasses1DataContext(conn))

    {

       //paste your code here...

    }

     

     

     

    It can also be a good idea to wrap it in a TransactionScope and turn off identity insert again when you're done. (Or you may end up with a pooled connection with identity insert left on)

    Tuesday, October 28, 2008 5:22 AM
    Answerer
  • There is a property of Column attribute (of System.Data.Linq.Mapping namespace) called IsDbGenerated. When you add this property for a Column and set it to true, it says the DataContext that column will be filled by the database i.e. its value will be generated by the database. This is very useful for Identity Columns. In this case, you also donot required to set the IDENTITY_INSERT on. So, suppose we have an identity column say "UserID", and we want to insert a row in our database table named say "TestTable". The code to do this is as follow

    //In the class TestTable (which is mapped for LINQ)
    [Column (Name="UserID",IsDbGenerated=true,DbType="int Not Null IDENTITY")]
    public int UserID
    {

    get;
    set;
    }

    //In the class where you want to insert row into table TestTable
    TestDBDataContext tdb=new TestDBDataContext(ConnString);
    tdb.TestTable.
    InsertOnSubmit(newRow);
    tdb.
    SubmitChanges();


    In this way you can insert a new row.


    Monday, December 08, 2008 6:15 PM