LINQ to SQL is not limited itself only for mapping objects and relational data. It also plays an important roles for managing the entity life cycle ,that is tracking change for each entity by service tracking ,and handle when there are conflicts. Today we gonna learn more in this features LINQ to SQL provides.
When you query some values from the database (with DataContext) ,at that time before giving those values's object to application code to consume ,DataContext will retain a referrence to the object. Therefore ,DataContext can refer back to that object and track it by the identity management service. If two queries want to retrieve the same value from the database ,the second query will get the object that's returned by the first query from the cache rather than remapping with SQL database again this is one crucial task of DataContext. When that object is queried there's no saving the data back to the database until we issue the command "SubmitChanges" to save that data. You must know that there are the data in memory and the data in the database and they're seperated.
If I create two DataContexts(context1,context2) then I submit changes on context1 ,context1 will commit the chages to the database and its service tracking will be flushed and it'll use the newly changed value. But for context2 ,it retains to use its old value from its service tracking because its service tracking's not flushed as same as the context1s'.
TestLINQToSQLDataContext context1 = new TestLINQToSQLDataContext();
TestLINQToSQLDataContext context2 = new TestLINQToSQLDataContext();
Console.WriteLine("Before SubmitChanges");
Console.WriteLine(context1.Teachers.SingleOrDefault().Name);
Console.WriteLine(context2.Teachers.SingleOrDefault().Name);
//change the value and submit change via context1
Teacher teacher = context1.Teachers.SingleOrDefault();
teacher.Name = "Dr.Ochin";
context1.SubmitChanges();
Console.WriteLine("After SubmitChanges");
Console.WriteLine(context1.Teachers.SingleOrDefault().Name);
Console.WriteLine(context2.Teachers.SingleOrDefault().Name);
Because DataContext makes use of optimistic concurrency principle(it's not serialize and lock the occupied data) ,so that can bring on some conflicts when the same object identity is modified by more than two people. DataContext must have a way to control the degree of conflict detection by setting the "UpdataCheck" value of each column which has 3 properties : "Always" ,"Never" ,"WhenChanged". "Always" means that LINQ to SQL always checks that column whether or not its actual value's been changed that means the column's name will always appear in WHERE clause of a SQL UPDATE statement generated by LINQ to query the database(to check if that column is changed). For example ,if I set "Always" to "Name" column ,this query will have this "Name" in WHERE clause everytime when submitting changes : UPDATE SET .... FROM ... WHERE ... Name=@p4 ... .If we're sure which columns don't need to participate in WHERE clause ,we can exclude it by setting "UpdateCheck" to "Never". For the "WhenChanged" property ,imagine that two users simultaneously want to update on the same table but on different columns (if using "Always", there'll be a conflict) ,thus your choice'll be "WhenChanged". But again that is not the best choice ,especially when you have a "derived field" (field that its value is calculated from other fields) e.g. Mr.Suppy is trying to update the price field of the product A while Mr.Ochin is changing its quantity field ,the "TotalPrice" field might not be correct.
Ok let's move ahead and see how we can manage if there're concurrency conflicts occurred between 2 users. In that case ,DataContext will raise "ChangeConflictException" if the second user tries to submit changes. In fact ,you can simply just specify "ConflictMode" ,which has two options ConflictMode.ContinueOnConflict and ConflictMode.FailOnFirstConflict(stop evaluating further) ,and "RefreshMode" to handle the problem. I've created simple scenarios for you to understand. Note that sometimes when conflict occurs there're 3 values you must consider : original value(which is cached in its DataContext at the early time) ,changed value(the new value we've changed) ,current database value(actual value stored in database).
TestLINQToSQLDataContext context1 = new TestLINQToSQLDataContext();
TestLINQToSQLDataContext context2 = new TestLINQToSQLDataContext();
Console.WriteLine("Before SubmitChanges");
Teacher t1 = context1.Teachers.SingleOrDefault();
Console.WriteLine(t1.Name+" || "+t1.School+" || "+t1.Subject);
Teacher t2 = context2.Teachers.SingleOrDefault();
Console.WriteLine(t2.Name + " || " + t2.School + " || " + t2.Subject);
//the current teacher's name in database is "Dr.Database","DatabaseSchool","Database Management" respectively
t1.Name = "Dr.Context1";
t1.Subject = "Context1 Subject";
t2.Name = "Dr.Context2";
t2.School = "Context2 School";
//just submit the value to the database for context1 first.
context1.SubmitChanges();
//try to submit by context2 with ConflictMode
try
{
context2.SubmitChanges(ConflictMode.ContinueOnConflict); //conflict occurs!!!
}
catch(ChangeConflictException)
{
context2.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
context2.SubmitChanges();
}
Console.WriteLine("After SubmitChanges");
Console.WriteLine(t1.Name + " || " + t1.School + " || " + t1.Subject);
Console.WriteLine(t2.Name + " || " + t2.School + " || " + t2.Subject);
The first example one is for RefreshMode.KeepChanges : (from MSDN)Forces the Refresh method to keep the current value that has been changed, but updates the other values with the database values.
The output is as we've expected. The others 2 ConflictMode are RefreshMode.KeepCurrentValues : Forces the Refresh method to swap the original value with the values retrieved from the database. No current value is modified.
And RefreshMode.OverwriteCurrentValues : Forces the Refresh method to override all the current values with the values from the database.
"ChangeConflictException" isn't only that useful but it can tell you what are the names of table and column that's occurred the conflicts and show the comparison between the original value and the value to be change as the code suggests below.
catch(ChangeConflictException)
{
context2.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
GridView1.DataSource =
from conflict in context2.ChangeConflicts
from member in conflict.MemberConflicts
select new {
MemberName = member.Member.Name,
ChangedValue = member.CurrentValue.ToString(),
OriginalValue = member.OriginalValue.ToString(),
DatabaseValue = member.DatabaseValue
};
GridView1.DataBind();
}
Using the same scenario as above ,we'll get this result and we'll know that which fields and values cause the problem. You can apply this code to match your need.
What if we don't want those three default RefreshModes or we don't want to change anything when conflict occurs ,we want to roll it back! One of the popular solutions is using System.Transactions.TransactionScope that comes with ASP.NET 2.0. The most awesome thing about this transaction scope is it can scope the transaction to match with the object that requires it. If there's multiple connections or DataContext objects ,it'll span its transaction scope. Another thing is you don't need to catch its exception and type "Rollback" manually ,it'll do that for you.
For the transaction scope ,if the Complete() method is called that means it reaches the checkpoint and you cannot roll back anymore but if conflict happens ,the Complete() method'll be bypassed. I'll show how to write the transaction scope below and with this thing ,my database'll be rolled back to its original value.
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
try
{
context1.SubmitChanges();
//conflict'll occur here and it'll roll back as if nothing happened..
context2.SubmitChanges(ConflictMode.ContinueOnConflict);
ts.Complete();
}
catch (ChangeConflictException)
{
context2.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
GridView1.DataSource =
from conflict in context2.ChangeConflicts
from member in conflict.MemberConflicts
select new
{
MemberName = member.Member.Name,
ChangedValue = member.CurrentValue.ToString(),
OriginalValue = member.OriginalValue.ToString(),
DatabaseValue = member.DatabaseValue
};
GridView1.DataBind();
}
}
This code provided here is not a good solution because you should avoid using multiple database connections in one transaction scope ,it creates overhead and it's not recommended but just for me to show you that we can do like this and can also roll back them all. If you have the problem that you cannot use multiple connections in transcation scope ,i.e. MSDTC on server 'XXXXXX' is unavailable. You have to start the service called "Distributed Transaction Coordinator" to enable using MSDTC first in Control Panel > Adminitrative Tools > Services. And again it's not recommended when using in a real world application.
Transaction scope can be used with StoreProcedure as well,see more : http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic20
Enough for today!! I'll continue next with one remaining topic : When you work with disconnected data. Thanks for reading...
bd0d1789-fb1c-4019-bacb-ecc07cb73380|0|.0