The question is solely about rolling back the changes, not commiting.

Let's say I fetch some data, I change them, I submit changes (optional step) and I roll back transaction. Wherever you look every author writes, this cancels the changes.

But I found out that is half true -- LINQ DataContext will keep the changed data! I tested this using TransactionScope and DataContext.Transaction. In both cases I got the same behaviour.

A workaround would be to recreate DataContext after roll back (however this leads to other problems like caching data and handling nested transactions) or manually discarding the changes in DataContext. Nevertheless those are just workarounds.

Questions

So what am I missing? Is LINQ to SQL not suited for transactions? How to use transactions so they would REALLY roll back changes?

Example

                MyTable record = null;

                db.Connection.Open();
                using (db.Transaction = db.Connection.BeginTransaction())
                {
                        record = db.MyTable.First();
                        record.BoolField = !record.BoolField; // changed
                        db.SubmitChanges();
                        db.Transaction.Rollback();
                }

A data-context should be considered as a unit-of-work. How granular you make that is up to you - it could be a page request, or a single operation; but - if you get an exception (or pretty much anything unexpected) - stop; abandon the data-context and rollback. After a rollback, your data-context is going to be confused, so just don't keep it.

Additionally; don't keep a data-context for longer than necessary. It is not intended as an app-long data cache.

I am terrible with databases so please bear with me.

I have a program that gets some user information and adds it to a database table. I then later need to get more information for that table, and update it. To do so I have tried doing this:

    public static void updateInfo(string ID, string email, bool pub)
    {
        try
        {
            //Get new data context
            MyDataDataContext db = GetNewDataContext(); //Creates a new data context

            //Table used to get user information
            User user = db.Users.SingleOrDefault(x => x.UserId == long.Parse(ID));

            //Checks to see if we have a match
            if (user != null)
            {
                //Add values
                user.Email = email;
                user.Publish = publish;
            }

            //Prep to submit changes
            db.Users.InsertOnSubmit(user);
            //Submit changes
            db.SubmitChanges();
        }
        catch (Exception ex)
        {
            //Log error
            Log(ex.ToString());
        }
    }

But I get this error:

System.InvalidOperationException: Cannot add an entity that already exists.

I know this is because I already have an entry in the table, but I don't know how to edit the code to update, and not try to make a new one?

Why does this not work? Wouldn't submitting changes on a current item update that item and not make a new one?

The problem is

//Prep to submit changes
db.Users.InsertOnSubmit(user);

Because you got the user from the DB already, you don't need to re-associate it with the context.

Comment that out and you're good to go.

Just a style / usage comment as well. You should dispose your context:

public static void updateInfo(string ID, string email, bool pub)
{
    try
    {
        using (MyDataDataContext db = GetNewDataContext()) 
        {
            User user = db.Users.SingleOrDefault(x => x.UserId == long.Parse(ID));

            if (user != null)
            {
                user.Email = email;
                user.Publish = publish;
            }

            db.SubmitChanges();
        }
    }
    catch (Exception ex)
    {
        //Log error
        Log(ex.ToString());

        // TODO: Consider adding throw or telling the user of the error.
        // throw;

    }
}