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
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.