How to find number of rows affected in LINQ to SQL?

Question

Does anybody know how to find the number of rows affected AFTER I have submitted changes to the data context in LINQ to SQL?

At first I was doing something like this:

Using db as New MyDataContext()
    db.Users.Attach(modifiedUser, True)
    db.SubmitChanges()

    Dim rowsUpdated As Integer = db.GetChangeSet().Updates.Count
End Using

I have since figured out that this doesn't work and that

db.GetChangeSet().Updates.Count

Only tells you how many updates there will be BEFORE you call SubmitChanges().

Is there anyway to find out how many rows have actually been affected?

Answer 1

I have not worked on LINQ to SQL. But, I think it might not be possible.

The reason that comes to mind is: you could do updates to multiple entities before calling SubmitChanges. So, which "records affected" you are looking for won't be known, I guess.

Answer 2

Because a number of different operations could potentially be committed, it's highly unlikely that you'll be able to get back that kind of information.

The SubmitChanges() command will commit inserts, updates and deletes and as far as I can tell there's no way to retrieve the number of rows affected for each (# rows deleted/updated/inserted etc). All you can do is see what is going to be committed, as you've already discovered.

If you had one operation in particular you wanted to perform you could use the ExecuteCommand() method which returns the affected row count.

Answer 3

L2S issues individual insert/update/delete statements for each row affected, so counting entities in the GetChangeSet results will give you the correct 'rows affected' numbers*.

If any row can not be updated due to a change conflict or similar, you'll get an exception during submitchanges, and the transaction will be rolled back.

(* = ...with one exception; if you have any updatable views with instead-of-triggers you could potentially have a situation where the instead-of-trigger hits multiple underlying rows for every row updated. but that is a bit of an edge case... :) )