How to create a LINQ to SQL Transaction?

Question

I have a piece of code that involves multiple inserts but need to execute submitchanges method before I finish inserting in other tables so that I can aquire an Id. I have been searching through the internet and couldnt find how to create a transaction in linq to sql. I have put comments in the code where I want the transaction to take place.

    var created = false;
    try
    {
        var newCharacter = new Character();
        newCharacter.characterName = chracterName;
        newCharacter.characterLevel = 1;
        newCharacter.characterExperience = 0;
        newCharacter.userUsername = userUsername;
        newCharacter.characterClassID = ccslst[0].characterClassID;
        //Open transaction


            ydc.Characters.InsertOnSubmit(newCharacter);
            ydc.SubmitChanges();

            foreach (var ccs in ccslst)
            {
                var cs = new CharacterStat();
                cs.statId = ccs.statID;                        
                cs.statValue = ccs.statValue;
                cs.characterID = newCharacter.characterID;
                ydc.CharacterStats.InsertOnSubmit(cs);
            }                    


            var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
            foreach (var ccb in ccblst)
            {
                var charBody = new CharacterBody();
                charBody.bodyId = ccb.bodyId;
                charBody.bodyPartId = ccb.bodyPartId;
                charBody.characterID = newCharacter.characterID;
                ydc.CharacterBodies.InsertOnSubmit(charBody);
            }
            ydc.SubmitChanges();      
            created = true;
        //Commit transaction
        }
        catch (Exception ex)
        {
            created = false;
            //transaction Rollback;                    
        }
        return created;

EDIT: Forgot to mention that ydc is my datacontext

score 72 · Accepted Answer · edited Dec 13 '15 at 00:11

72

Wrap the whole thing in a TransactionScope. Call transaction.Complete() at the point where you want to commit. If the code exits the block without Complete() being called, the transaction will be rolled back. However, after looking at @s_ruchit's answer and re-examining your code, you could probably rewrite this to not require a TransactionScope. The first example uses the TransactionScope with your code as is. The second example makes some minor changes, but accomplishes the same purpose.

A place where you would need to use the TransactionScope is when you are reading a value from the database and using it to set a new value on an object being added. In this case the LINQ transaction won't cover the first read, just the later submit of the new value. Since you are using the value from the read to calculate a new value for the write, you need the read to be wrapped in the same transaction to ensure that another reader doesn't calculate the same value and obviate your change. In your case you are only doing writes so the standard LINQ transaction should work.

Example 1:

var created = false;

using (var transaction = new TransactionScope())
{
    try
    {
        var newCharacter = new Character();
        newCharacter.characterName = chracterName;
        newCharacter.characterLevel = 1;
        newCharacter.characterExperience = 0;
        newCharacter.userUsername = userUsername;
        newCharacter.characterClassID = ccslst[0].characterClassID;

        ydc.Characters.InsertOnSubmit(newCharacter);
        ydc.SubmitChanges();

        foreach (var ccs in ccslst)
        {
            var cs = new CharacterStat();
            cs.statId = ccs.statID;                        
            cs.statValue = ccs.statValue;
            cs.characterID = newCharacter.characterID;
            ydc.CharacterStats.InsertOnSubmit(cs);
        }                    

        var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
        foreach (var ccb in ccblst)
        {
            var charBody = new CharacterBody();
            charBody.bodyId = ccb.bodyId;
            charBody.bodyPartId = ccb.bodyPartId;
            charBody.characterID = newCharacter.characterID;
            ydc.CharacterBodies.InsertOnSubmit(charBody);
        }
        ydc.SubmitChanges();      
        created = true;

        transaction.Complete();
    }
    catch (Exception ex)
    {
        created = false;
    }
}
return created;

Example 2:

    try
    {
        var newCharacter = new Character();
        newCharacter.characterName = chracterName;
        newCharacter.characterLevel = 1;
        newCharacter.characterExperience = 0;
        newCharacter.userUsername = userUsername;
        newCharacter.characterClassID = ccslst[0].characterClassID;

        ydc.Characters.InsertOnSubmit(newCharacter);

        foreach (var ccs in ccslst)
        {
            var cs = new CharacterStat();
            cs.statId = ccs.statID;                        
            cs.statValue = ccs.statValue;
            newCharacter.CharacterStats.Add(cs);
        }                    

        var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
        foreach (var ccb in ccblst)
        {
            var charBody = new CharacterBody();
            charBody.bodyId = ccb.bodyId;
            charBody.bodyPartId = ccb.bodyPartId;
            newCharacter.CharacterBodies.Add(charBody);
        }
        ydc.SubmitChanges();      
        created = true;
    }
    catch (Exception ex)
    {
        created = false;
    }

edited Dec 13 '15 at 00:11

George Lanetz

300
5
18

answered Apr 16 '09 at 11:56

tvanfosson

524,688
99
697
795

Not sure about the syntax , do I have put my code in this scope : using (TransactionScope ts = new TransactionScope()) { //my code } – Drahcir Apr 16 '09 at 12:02
2

You will Require DTC service to run on the deployment machine to perform transactions using TransactionScope. Keep that in considertation. DTC: Distributed Transaction Co-ordinator Service. – this. __curious_geek Apr 16 '09 at 12:24
@s_ruchit -- actually I'm not sure that with LINQ it will get promoted to a distributed transaction. Since the data context uses the same connection for all the submits I think it will stay local. This is different than when using TableAdapters. – tvanfosson Apr 16 '09 at 13:18
@tvanfosson -- If you are using TransactionScope, you will surely require DTC service but in case of LinqToSql it performs all operations on Single Connection so it does not depend on DTC. I prefer TransactionScope on 2nd priority since it has dependancy on DTC. – this. __curious_geek Apr 16 '09 at 14:16
@s_ruchit -- I'm pretty sure that it won't promote to DTC unless there is more than one connection involved. I spent a lot of time debugging a TableAdapter application that used TransactionScope and would only fail under certain conditions. We eventually determined that it was a firewall issue... – tvanfosson Apr 16 '09 at 14:53
...and that DTC needed to be opened when more than one table adapter was involved (there was conditional logic inside the scope). When only one table adapter was involved it didn't promote and we didn't see the problems. The worst part was we didn't discover until we went to production... – tvanfosson Apr 16 '09 at 14:54
...because only the production server was firewalled from my web server. – tvanfosson Apr 16 '09 at 14:55
yeah, I agree. dtc is only required if more than one connection participates in the transaction. if you can ensure that another connection wont be created inside the transaction then this will work just fine. If you are using other components inside the transaction, be sure that they will not create any connections because it will throw an exception if DTC is not enabled. – Mel Jan 31 '12 at 07:18
Would it work if I use different data context objects in my transaction? – David Levin Feb 22 '12 at 18:33
@EvgenyLevin using multiple contexts to talk to the same database is a bad idea. The context's each "own" their objects and I don't think you can get them to play together very well. You might get them to share a connection, but I think you'll have problems if they try to work on the same data. – tvanfosson Feb 22 '12 at 19:00
TransactionScope from which namespace: `System.Activities.Statements` or `System.Transactions`? I have never used either before. – flipdoubt Apr 08 '13 at 13:34
1

@flipdoubt if you're using Workflow Foundation, then use `System.Activities.Statements`, otherwise use `System.Transactions`. – tvanfosson Apr 08 '13 at 13:50
WARNING! The default values for TransactionScope can cause deadlocks. To fix this use ReadCommitted isolation level: http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspx – Thorgeir Sep 01 '14 at 08:39
Starting EF6 with the new [Database.BeginTransaction()](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.begintransaction) and [Database.UseTransaction()](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.usetransaction) APIs, the **TransactionScope** approach is no longer necessary for most users. – Paul Nakitare Sep 25 '21 at 15:20

score 45 · Answer 2 · edited Oct 05 '12 at 09:07

45

You do not need to do explicit Transaction Implementation while using LINQ to SQL. All DB operations are wrapped in a transaction by default.

Ex:

AppDataContext db = new AppDataContext();

<In memory operation 1 on db>
<In memory operation 2 on db>
<In memory operation 3 on db>
<In memory operation 4 on db>

db.SubmitChanges();

All operations between db DataContext initialization and db.SubmitChanges() are wrapped around a Database Transaction by .Net ensuring your database to be in consistent and with property integrity maintained across tables.

Read an article By Scott Guthrie here :- http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

edited Oct 05 '12 at 09:07

Ruben Bartelink

59,778
26
187
249

answered Apr 16 '09 at 12:04

this. __curious_geek

42,787
22
113
137

But needed an ID that was generated on the first submit changes, didn't know otherwise how to aquire the id. – Drahcir Apr 16 '09 at 12:11
4

Visit the article written by ScottGu. You can associate without the ID. That's the beauty of LINQ-to-SQL. If you're not doind that way, you're not leveraging the Linq-To-Sql. I recommend you to read ScottGu's article. You can find the link in my Answer. – this. __curious_geek Apr 16 '09 at 12:20
Yes. It looks like you could rewrite this to not require an outer transaction. I'll update my answer. – tvanfosson Apr 16 '09 at 13:21
1

+1 it seems that LINQ to SQL resolves all of the dependencies for you on commit. Nice :) – satnhak Nov 05 '11 at 22:32
It means that if some of the changes lead to failure, all previous changes will rollback? – VSB Feb 10 '18 at 13:21

score -1 · Answer 3 · answered Sep 25 '21 at 15:09

EF6 provides Database.BeginTransaction() - an easier method for a user to start and complete transactions themselves within an existing DbContext – allowing several operations to be combined within the same transaction and hence either all committed or all rolled back as one. It also allows the user to more easily specify the isolation level for the transaction. DbContextTransaction.Commit() method performs commit on the underlying store transaction whereas DbContextTransaction.Rollback() method performs rollback on the underlying store transaction.

var created = false;
using (var ydc = new CustomContext())
{
    using (var dbContextTransaction = ydc.Database.BeginTransaction())
    {
        try
        {
            var newCharacter = new Character();
            newCharacter.characterName = chracterName;
            newCharacter.characterLevel = 1;
            newCharacter.characterExperience = 0;
            newCharacter.userUsername = userUsername;
            newCharacter.characterClassID = ccslst[0].characterClassID;
            //Open transaction

            ydc.Characters.InsertOnSubmit(newCharacter);
            ydc.SubmitChanges();

            foreach (var ccs in ccslst)
            {
                var cs = new CharacterStat();
                cs.statId = ccs.statID;
                cs.statValue = ccs.statValue;
                cs.characterID = newCharacter.characterID;
                ydc.CharacterStats.InsertOnSubmit(cs);
            }

            var ccblst = ydc.ClassBodies.Where(cb => cb.characterClassID == newCharacter.characterClassID);
            foreach (var ccb in ccblst)
            {
                var charBody = new CharacterBody();
                charBody.bodyId = ccb.bodyId;
                charBody.bodyPartId = ccb.bodyPartId;
                charBody.characterID = newCharacter.characterID;
                ydc.CharacterBodies.InsertOnSubmit(charBody);
            }
            ydc.SubmitChanges();
            created = true;
            // Commit transaction
            dbContextTransaction.Commit();
        }
        catch (Exception ex)
        {
            created = false;
            // Rollback transaction
            dbContextTransaction.Rollback();
        }
    }
}
return created;

It's a LINQ-to-SQL question. – Gert Arnold Sep 25 '21 at 18:20 — Gert Arnold, Sep 25 '21 at 18:20

How to create a LINQ to SQL Transaction?

3 Answers3

Linked