How do I disable LINQ to Sql transactions?

Question

I want to make additions to a database, in this case massive amounts of information, with no transactions on my LINQ To SQL model. Does anyone know how to turn off LINQ To SQL transactions on a datacontext or on the submit changes operation?

Answer 1

Can't you just set the Transaction property on DataContext to null?

Answer 2

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

Be careful though, since you can only recover a database with a simple recovery mode to the latest backup and won't be able to apply the transaction logs from transactions that occurred since that backup was performed.

If you can configure a batch size (I know you can with NHibernate, not positive about LINQ to SQL) to something like 100 or more, that can also reduce the round trips to the database which will also increase insert performance.

Answer 3

Since LINQ to SQL will create a transaction if one doesn't exist, the best you can do is create your own transaction with 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();
}

Alternatively, you could submit on each change, ignoring failures. You still get a transaction, but it only wraps each individual change.

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

Answer 4

Set the ConflictMode to ContinueOnConflict and you will be able to commit successfully any changes that work, and you'll have a list at the exception of changes that weren't successful for you to follow up.

db.SubmitChanges(ConflictMode.ContinueOnConflict)

I've got a reference to using this technique to stop errors backing out your submit changes: SandKeySoftware Linq Tutorial

If you have distributed transactions occurring, this should help too:

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