Save without commit. Is it possible?

[mpovidlov]

I have multiple database constraints plus some triggers that, say, autoincrement IDs. Before I commit I'd like to make sure that everything is OK without saving into the database. Otherwise, if error occurs in the middle, I will end up with incompletely loaded data and will have to clean it manually.
I can see only SaveChanges() method of the Entities to send data to the database but it commits automatically. Is there a way to save the data temporarily to the database with possible rollback without commit?

[AndreyR]

In fact, there is no way to update DB objects in Entity Framework without using the SaveChanges() method.
But as an alternative I advise you to raise an exception in your trigger, so changes will be rolled back if anything goes wrong.

[mpovidlov]

In fact, there is no way to update DB objects in Entity Framework without using the SaveChanges() method.
But as an alternative I advise you to raise an exception in your trigger, so changes will be rolled back if anything goes wrong.

Thanks for answering, Andrey.
I have two tables, as an example: Table1 (that contains look-up values) and Table2 that refers to them by FK.
First, I need to populate Table1. This will create PK Id's by calling the trigger. I can activate the trigger only by SaveChanges() method. This will save the data permanently in db. Now, I populate Table2 where I can make an error and raise an exception. The exception will not rollback the data entered to Table1, or am I wrong? The error may have occured because the data in Table1 were incorrect. This is where I would need to be able to commit only at the end of loading all the data.

[AndreyR]

Try calling the SaveChanges() method not after the first insert, but after the whole group, like in the following code:

Code: Select all

      using (Entities db = new Entities()) {

        AUTOINC a = new AUTOINC {
          FIELD = "test",
        };
        AUTOINC_DETAILS ad = new AUTOINC_DETAILS {
          DESCRIPTION = "test",
          DETAIL_ID = a.ID
        };
        a.AUTOINC_DETAILS = new System.Data.Objects.DataClasses.EntityCollection{ad};
        db.AddToAUTOINC(a);
        db.SaveChanges();

Please note that the StoreGeneratedPattern property of the autoincrement columns should be set to "Identity" in order to use triggers correctly.
The mappings should be like the following:

Code: Select all