EntityFramework transaction rollback with multi table insert

Question

Question: I need to get an identity back in a multi table insert, and I need to wrap transaction support around it in Entity Framework.

I have two (psuedo generic) objects with corresponding tables, Book and Author:

create table Author
(authorid int identity primary key,
authorname varchar(max)
)

create table Book
(bookid int identity primary key,
bookname varchar(max),
authorid int references Author(authorid)
)

My problem is that when I need to insert a new book with a new author, I end up needing to do something like this, and if the Book insert throws an exception I have an Author with no Book which isn't good for my application.

context.Authors.Add(newauthor);
context.SaveChanges();
newbook.AuthorID = newauthor.ID //I can read this now because the SaveChanges() created the ID
context.Books.Add(newbook);
context.SaveChanges();

I skimmed over this article which basically says to not use Transactions with EntityFramework and advises to call SaveChanges() once per operation and let EF handle transactions by itself. I'd love to, but I need to get the identity from the table back first, like indicated in my psuedo code and this SO question

Answer 1

Question is - do you absolutely need ID of inserted author?

You can develop with Entity either with code first or with db first. In case you're using db first, you will have .edmx file with generated entities, with navigation properties and collections... So, what's the point of above - key feature, for Author entity you will have Books collection, thanks to relationship authorid int references Author(authorid) in your table Book. So, to add book to author, just make something like:

//Somewhere here author is created, add it to context.Authors
context.Authors.Add(newauthor);

//Somewhere here book is created, don't need to add it to context.Books; don't call SaveChanges either
newauthor.Books.Add(newbook);// And this is all you need; ID management will be done by Entity, automatically

context.SaveChanges(); // Only one call to save changes you will need
//Of course, all IDs of inserted entities will be available here... 

Similar structure will be also valid for code first; in Author entity you will, most likely, have public virtual ICollection<Book> Books collection. And creation of book will be done in same way as described above.

While you can, of course, make multiple context.SaveChanges() to get ID of newly inserted entity - you should not do it. Each SaveChanges() just ads round-trip to server, and, probably, you will end up with poor performance. Better to leave management of ID values to Entity, as described above.

And, to finish the story. With structure above, EF automatically wraps everything up-to-SaveChanges() in transaction. So, if Book insertion will fail, Author insertion will also be undone.

Answer 2

If you really don't want to use transactions in code, then you can wrap everything in a stored procedure. However, just because the default isolation level is serializable, there's no reason you can't change it:

using(var scope = new TransactionScope(
        TransactionScopeOption.RequiresNew,
        new TransactionOptions() {
                IsolationLevel = IsolationLevel.ReadCommitted
            })) {
    context.Authors.Add(newauthor);
    context.SaveChanges();
    newbook.AuthorID = newauthor.ID
    context.Books.Add(newbook);
    context.SaveChanges();
    scope.Complete();
}

That said you don't usually need to do this manually, as per Dmitriy's answer.