How can i get next ident ID before submit in Linq-to-sql

Question

I mean i want to get next ID, which is identity, log it somewhere and only then do SubmitChanges()

Answer 1

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

Answer 2

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.

Answer 3

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.
            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();
    }
}