How to disable LINQ to Sql transactions?

Question

I want to make additions to the database, in this case huge amounts of information, without transactions in my LINQ To SQL model. Does anyone know how to disable LINQ To SQL transactions in a datacontext or in a change submit operation?

Answer 1

As far as I can tell, there is no way to disable transactions on insertion without changing the database recovery mode from full to simple.

Be careful, because you can restore a database with a simple recovery mode to the last backup and you will not be able to apply transaction logs from transactions that have occurred since this backup was performed.

If you can adjust the packet size (I know you can with NHibernate, and not have a positive LINQ to SQL attitude) to something like 100 or more, which can also reduce round trips to the database, which will also increase insert performance.

Answer 2

Since LINQ to SQL will create a transaction if it does not exist , it is best to create your own transaction using an isolation level that is acceptable to you.

Transaction transaction = new Transaction( IsolationLevel.Chaos );
try
{
    using (var dc = new MyDataContext())
    {
       dc.Transaction = transaction;
       ...
       transaction.Commit();
    }
}
catch
{
    transaction.Rollback();
}

, . - , .

foreach (var foo in foos)
{
    using (var context = new MyDataContext())
    {
        context.Foos.InsertOnSubmit(foo);
        try
        {
            context.SubmitChanges();
        }
        catch {}
    }
}

Answer 3

Transaction DataContext null?

Answer 4

Set ConflictMode for ContinueOnConflict, and you can successfully make any changes that will work, and you will have a list with the exception of changes that you failed to complete.

db.SubmitChanges(ConflictMode.ContinueOnConflict)

I have a link to using this technique to stop errors that undo your changes: SandKeySoftware Linq Tutorial

If you received distributed transactions, this should also help:

using(var scope = new TransactionScope(TransactionScopeOption.Suppress))
{
    //linqy stuff here
}