I am starting out learning all of this. This is my code:
using (var context = new developContext())
using (TransactionScope transaction = new TransactionScope())
consecs c = context.consecs.First(i => i.consec >= -1);
consecs c2 = context.consecs.First(i => i.consec >= -1);
I open the connection before the transaction because if I do this the other way around, it escalates to a distributed transaction from the beginning. Then I issue the first query. I just wanted to get the only record in the table with only one field, so I used that EF syntax for it.
The problem is, this select closes the connection when it's done, so the second select tries to reopen the connection with a distributed transaction, and fails.
The example I was going for with transactions working, is:
update consecs set consec = consec+1; //update and lock the table
select consec from consecs; // should be the updated value
rollback; // undo all of this so the example is repeatable without altering the database
But I am getting stuck with trying to keep the transaction local, even if I'm not making an update yet.