Discard changes in LINQ to SQL DataContext
The LINQ to SQL DataContext provides excellent functionality for managing a set of local changes to a database that can be pushed to the server with a single call to SubmitChanges(). Inevitably there will be situations where you want to discard the changes you have made, effectively allowing you to continue using the DataContext as though those changes had never been made. Unfortunately, there is no DataContext.DiscardChanges() method.
A little research reveals that this is by design and that you should simply recreate the DataContext in these cases, but of course, nothing is that simple. Every object you have that came from the original DataContext now needs to be reset to use the new one to guarantee predictable behaviour. For example, the last line of this snippet will set objectsAreEqual to False:
' Create two data contexts Dim dc1 As New UsersDataContext Dim dc2 As New UsersDataContext ' Grab the same record out of both the data contexts Dim user1 = dc1.Users.First Dim user2 = dc2.Users.First ' False, because the otherwise identical records come from different contexts Dim objectsAreEqual = user1 = user2
Basically, this shows that it does matter where your object came from. If you just want to discard a few little changes and you don’t want to have to recreate all your variables, you can instead undo all the changes that you have made so far and continue to use the same DataContext.
Undoing inserts and deletes is easy, because we can just do the opposite to revert the change. Luckily there is even a nice function on the DataContext that will show us what we need to do called GetChangeSet:
Public Sub DiscardInsertsAndDeletes(ByVal data As DataContext) ' Get the changes Dim changes = data.GetChangeSet() ' Delete the insertions For Each insertion In changes.Inserts data.GetTable(insertion.GetType).DeleteOnSubmit(insertion) Next ' Insert the deletions For Each deletion In changes.Deletes data.GetTable(deletion.GetType).InsertOnSubmit(deletion) Next End Sub
LINQ to SQL is smart enough to know that if a row is inserted and then deleted (or the other way around) nothing needs to be done. If you look at the ChangeSet after running this function, you will notice that the Inserts and Deletes collections are empty. Updates are a little more annoying. If you update a value, and then set it back to its original state then you will get the expected behaviour (ie: the ChangeSet will be empty because you reverted your change) but unless you write code to keep track of the original value yourself, there is no way to automatically put an object back into its start state without hitting the database. If hitting the database is acceptable, you can always use the Refresh function to get rid of the updates:
Public Sub DiscardUpdates(ByVal data As DataContext) ' Get the changes Dim changes = data.GetChangeSet() ' Refresh the tables with updates Dim updatedTables As New List(Of ITable) For Each update In changes.Updates Dim tbl = data.GetTable(update.GetType) ' Make sure not to refresh the same table twice If updatedTables.Contains(tbl) Then Continue For Else updatedTables.Add(tbl) data.Refresh(RefreshMode.OverwriteCurrentValues, tbl) End If Next End Sub
Good stuff. Helped me a lot.
Doesn’t work if inheritance introduced in LINQ to SQL data model though…
I would recommend to introduce a function GetTableWithInheritance (see below for c# code) and replace each call to data.GetTable(…) with a call GetTableWithInheritance(data, …)
Regards,
Alex
The code:
public ITable GetTableWithInheritance(DataContext data, Type type)
{
try
{
return data.GetTable(type);
}
catch
{
return data.GetTable(type.BaseType);
}
}
Sorry, there was an error in the code of GetTableWithInheritance function.
Should be like this:
public ITable GetTableWithInheritance(DataContext data, Type type)
{
try
{
return data.GetTable(type);
}
catch
{
return GetTableWithInheritance(data, type.BaseType); // error was here
}
}
That’s true, if you are subclassing your table classes then GetTable() will not work when you pass it the type of one of your classes that was not auto generated (ie: a subtype of an auto generated class).
I avoid inheriting from my table classes in LINQ to SQL. If you want to do that then you would probably be better off with the ADO.NET entity framework.
For some reason the Refresh method is not doing anything and the insert deleted method is throwing an exception “Cant insert item that already exists.
Are you sure that you inserted the objects in the delete set, rather than the insert set?
Thank you for useful post — one question though. Why the same method with refresh does not work for inserts? I.e. I would like to make the discarding procedure uniform for all three types, so I thought the best pick would be to refresh all tables — altered by delete, insert or update.
Refresh() just reverts the properties on an entity to their original state based on values in the database. Basically, it is an operation on the entities that you pass in the parameter, not on the DataContext. The result is that it will update entity properties, but not necessarily that entity’s state in the DataContext.
If encapsulation is what you’re worried about, just make a functions that calls DiscardInsertsAndDeletes and DiscardUpdates.
Thank you very much for explanation. I have still problems with inserts though. Normally I work like this:
record = new Record();
db.Table.InsertOnSubmit(record);
// here setting all the values of the record
// here discard may occur (+jump off the procedure)
and despite the loop iterates through this table it does not discard changes.
As a workaround I introduced new bool value which tracks if this is new record or just edit, and just before submit I add
// here discard may occur (+jump off the procedure)
if (is_insert)
db.Table.InsertOnSubmit(record);
This works, but thanks to not using discarding inserts.
Unfortunately I found problem with discard along with transactions. Please take a look:
begin trans
insert
update (of inserted record)
*
commit trans
in case of failure, all changes should be discarded.
However since all actions are inside transaction, on failure the insert is rolled back, and there is no way to fetch the data back to record to discard update. As the result of DiscardChanges I get exception from Linq about non existing record.
I can make a workaround for this particular case, but it would be great if someone had an idea for general, smart solution.
Thanks Graemehill,
I searched a lot find a solution to discard all changes, now i got.
it was really helpful
thanks
Exactly what I was looking for… Great Job and Thank You!!!
Thanks for this, exactly what I needed. Saved me a great deal of time!
WOW, thanks this is great. I was just getting ready to go back to ADO. Saved me a ton of work.
Can anyone convert the DiscardInsertsAndDeletes() and DiscardUpdates() methods to C#? I can’t get data.GetTable(insertion.GetType).DeleteOnSubmit(insertion)
to work properly. Thank you.
The only reason I can think of that would cause that line to fail in C# is that you need to have parenthesis at the end of the GetType function. Here’s my conversion of that function. Sorry, the formatting will be all screwy.
public void DiscardInsertsAndDeletes(DataContext data)
{
var changes = data.GetChangeSet();
foreach (var insertion in changes.Inserts)
{
data.GetTable(insertion.GetType()).DeleteOnSubmit(insertion);
}
foreach (var deletion in changes.Deletes)
{
data.GetTable(deletion.GetType()).InsertOnSubmit(deletion);
}
}