Howto use transactions correctly?

[bairog]

For example I have the following code (extremely simplified):

Code: Select all

...
var connection = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
            {
                dbContext.Users.Add(new User() { Id = 1, Name = "111" });
                dbContext.SaveChanges();

                dbContext.Users.Add(new User() { Id = 2, Name = "122" });
                dbContext.SaveChanges();

                dbContext.Users.Add(new User() { Id = 3, Name = "333" });
                
                transaction.Commit();
            }
connection.Close();
...

1) Is that correct transaction usage scenario?
2) What data will be written to database? All 3 users, or only 2 (does transaction.Commit() performs dbContext.SaveChanges() automatically)?
3) Is there a difference in what dbContext.SaveChanges() actually do when it is called separately or within a transaction?

Thank you.

[Shalex]

1. Yes, that is.
2. Only 2.
3. Please enable the dbMonitor tool and compare the database activity in both cases.

[bairog]

Ok, I've got it.
And is there a diffference between calling SaveChanges() many times before commiting

Code: Select all

using (DbTransaction transaction = connection.BeginTransaction())
            {
                dbContext.Users.Add(new User() { Id = 1, Name = "111" });
                dbContext.SaveChanges();

                dbContext.Users.Add(new User() { Id = 2, Name = "222" });
                dbContext.SaveChanges();

                dbContext.Users.Add(new User() { Id = 3, Name = "333" });
                dbContext.SaveChanges();

                transaction.Commit();
            }

and calling SaveChanges() only one time just before the commit?

Code: Select all

using (DbTransaction transaction = connection.BeginTransaction())
            {
                dbContext.Users.Add(new User() { Id = 1, Name = "111" });                
                dbContext.Users.Add(new User() { Id = 2, Name = "222" });
                dbContext.Users.Add(new User() { Id = 3, Name = "333" });

                dbContext.SaveChanges();

                transaction.Commit();
            }

Finally 3 users will be written into database in both examples, but which one is better transaction using practice and why?

[Shalex]

Finally 3 users will be written into database in both examoles

That is correct. The generated SQL statements are equal in the both cases.

but which one is better transaction using practice and why?

It is better to use the second example because more entities attached to context means more time for validation of changes when each SaveChanges() is called.

[bairog]

Thx, I will use that recomendation.