LINQ to SQL not creating Transactions

Question

I've been searching everywhere, to try and get over this issue but I just can't figure this out.

I'm trying to make many changes to the DB with one single transaction using LINQ to SQL. I've created a .dbml that represents the SQL Table, then I use basicaly this code:

foreach (var _doc in _r.Docs)
{
    try
    {
        foreach (var _E in _Es)
        {
            Entity _newEnt = CreateNewEnt(_EListID, _doc, _fileName, _E);
            _db.Etable.InsertOnSubmit(_newEnt);
            _ECount++;

            if (_ECount % 1000 == 0)
            {
                _db.SubmitChanges();
            }
        }
    }
    catch (Exception ex)
    {
        throw;
    }
}

But when I do a SQL Profiler, the commands are all executed individually. It won't even start an SQL Transaction.

I've tried using TransactionScope (using statement and Complete()) and DbTransaction (BeginTransaction() and Commit()), none of them did anything at all, it just keeps on executing all commands individually, inserting everything like it was looping through all the inserts.

TransactionScope:

using(var _tans = new TransactionScope())
{
    foreach (var _doc in _r.Docs)
    {
        try
        {
            foreach (var _E in _Es)
            {
                Entity _newEnt = CreateNewEnt(_EListID, _doc, _fileName, _E);
                _db.Etable.InsertOnSubmit(_newEnt);
                _ECount++;
    
                if (_ECount % 1000 == 0)
                {
                    _db.SubmitChanges();
                }
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }
    _trans.Complete();
}

DbTransaction:

_db.Transaction = _db.Connection.BeginTransaction();
foreach (var _doc in _r.Docs)
{
    try
    {
        foreach (var _E in _Es)
        {
            Entity _newEnt = CreateNewEnt(_EListID, _doc, _fileName, _E);
            _db.Etable.InsertOnSubmit(_newEnt);
            _ECount++;

            if (_ECount % 1000 == 0)
            {
                _db.SubmitChanges();
            }
        }
    }
    catch (Exception ex)
    {
        throw;
    }
}
_db.Transaction.Commit();

I also tried commiting transactions everytime I Submit the changes, but still nothing, just keeps on executing everything individually.

Right now I'm at a loss and wasting time :\

Having a transaction is not equivalent to merging several insert statements into one. You still have all the separate statements, it's just that now you can roll them back. If you want bulk insert with Linq2Sql, see https://stackoverflow.com/q/9220901/11683. — GSerg, Jun 28 '21 at 12:32
Not sure if OP really expects merged insert statements. They seem to think the statements run individually as in, not in one transaction. @Tiago, I suggest, just for testing, you remove the Complete() (or Commit()) statement. I'd be surprised if anything is saved. How do you conclude that "It won't even start an SQL Transaction"? — Gert Arnold, Jun 28 '21 at 13:02
@GSerg was right, transactions do not run everything statement as one command, they run multilpe commands in one transaction, giving us the ability to rollback all those commands. In order to insert, update or delete multiple things, you need to do them in bulks — Tiago Almeida, Jun 28 '21 at 13:52

score 1 · Answer 1 · answered Jun 28 '21 at 14:03

GSerg was right and pointed me to the right direction, Transactions do not mean multiple commands in one go, they just allow to "undo" all that was made inside given transaction if need be. Bulk statements do what I want to do.

You can download a Nuget Package directly from Visual Studio called "Z.LinqToSql.Plus" that helps with this. It extends DataContext from LINQ, and allows to do multiple insertions, updates or deletes in bulks, which means, in one single statement, like this:

  foreach (var _doc in _r.Docs)
    {
        try
        {
            foreach (var _E in _Es)
            {
                Entity _newEnt = CreateNewEnt(_EListID, _doc, _fileName, _E);
                _dictionary.add(_ECount, _newEnt); //or using a list as well
                _ECount++;
    
                if (_ECount % 20000 == 0)
                {
                    _db.BulkInsert(_dictionary.Values); //inserts in bulk, there are also BulkUpdate and BulkDelete
                    _dictionary = new Dictionary<long, Entity>(); //restarts the dictionary to prepare for the next bulk
                }
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }

As in the code, I can even insert 20k entries in seconds. It's a very useful tool!

Thank you to everyone who tried helping! :)

LINQ to SQL not creating Transactions

1 Answers1