ASP MVC LINQ to SQLtransaction rollback

Question

I know that LINQ to SQL automatically wraps all changes in a database transaction. So if I wanted to use the returned ID for another insert (my user table has an AddressID, so I add a new Address record and then add a new user record with that ID) and there was a problem inserting a user, the address insert would not roll back. Should you wrap both SubmitChanges in another transaction?

Answer 1

LINQ to SQL will handle this for you if your tables have a foreign key relationship.

If you wish to use Visual Studios dbml designer you can follow the steps below

First drag the associated tables onto your designer and the one-to-many realtionship will be established by the addition of a UserAddress collection property to the User class.

This will allow you to create a UserAddress object and add it to your User object, then save the User object, LINQ to SQL will then handle the retrieval and insertion of the required primary keys as part of a transaction

User user = new User();
user.FirstName = "Foo";
user.LastName = "Bar";

UserAddress userAddress = new UserAddress();
userAddress.Line1 = "22";
userAddress.Line2 = "Acacia Ave";
userAddress.Postcode = "E13 9AZ";

user.UserAddresses.Add(userAddress);
db.Users.InsertOnSubmit(user);
db.SubmitChanges();

In the abridged version of the sql created during the submit of changes you can see that both updates to tables is contained within one transaction and so will be rolled back if any failure is encountered during execution (you can run a profiler against your db to view in depth the sql executed) .

BEGIN TRANSACTION 
INSERT INTO [dbo].[UserAddresses]([UserID], [Line1], [Line2], [Postcode]) VALUES (@p0, @p1, @p2, @p3)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
exec sp_executesql N'INSERT INTO [dbo].[UserAddresses]([UserID], [Line1], [Line2], [Postcode]) VALUES (@p0, @p1, @p2, @p3)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 int,@p1 nchar(10),@p2 nchar(10),@p3 nchar(10)',@p0=3,@p1=N'22        ',@p2=N'Acacia Ave',@p3=N'E13 9AZ   '
INSERT INTO [dbo].[Users]([FirstName], [LastName]) VALUES (@p0, @p1)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
exec sp_executesql N'INSERT INTO [dbo].[Users]([FirstName], [LastName]) VALUES (@p0, @p1)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 nchar(10),@p1 nchar(10)', @p0=N'Foo       ',@p1=N'Bar     
COMMIT TRANSACTION