CODEWITHSUNDEEP
linq-to-sql
Dmitry
Dmitry

Reputation: 53

How do I get the next identity ID before Submit in Linq-to-Sql?

I want to get the next identity ID and then log it somewhere. Only after this do I want to call SubmitChanges().

Upvotes: 2

Views: 3610

Answers (3)

Steven
Steven

Reputation: 172646

Wrap the DataContext in a database transaction and call SubmitChanges to write changes to the database within that transaction. This way you can get the auto generated ID while being able to keep the operation transactional:

using (var con = new SqlConnection(conStr))
{
    con.Open();
    using (var tran = con.BeginTransaction())
    {
        using (var db = new YourDataContext(con))
        {
            // Setting the transaction is needed in .NET 3.5. 
            // It's a bug in L2S and was fixed in .NET 4.0.
            db.Transaction = tran;

            var entity = new MyEntity();

            db.MyEntities.InsertOnSubmit(entity);

            db.SubmitChanges();

            var id = entity.Id;

            // Do something useful with this id
        }

        tran.Commit();
    }
}

Upvotes: 5

Chuck Haines
Chuck Haines

Reputation: 692

Wrap the whole thing in a transaction, do a SELECT IDENT_CURRENT('table_name') then submit your changes, the commit the transaction. If you lock the table that should prevent someone else from inserting a record after your SELECT IDENT_CURRENT and before you insert which should give you the correct identity value.

Upvotes: 1

anishMarokey
anishMarokey

Reputation: 11397

you need to add the lastModified date column in DB and get it,if not and increment the identity column

Other wise better do SubmitChanges() and get the nextID

Upvotes: 0

Related Questions