LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Most LINQ to SQL examples you find on the net tend to be written in a way that assumes a connected environment. I've found some blogs and posts that also talk about disconnected operations, but many of those are coming from the perspective of multiple tiers where the entity objects are being passed between tiers.

For this example, I've got a page that handles simple CRUD operations for a single employee record. It takes a query string parameter of "id". When it's not passed or "0" is passed, then it assumes you are going to be adding a new employee record, otherwise it loads the employee and allows you to edit it.

Note: I'm using module (page) level variables that are read from and stored in ViewState by overridden SaveViewState and LoadViewState. See my blog post on the subject.

Here's the OnLoad method...

protected override void OnLoad(EventArgs e)
{
 if (!IsPostBack)
 {
 _EmployeeID = Convert.ToInt32(Request.QueryString["id"] ?? "0");
 if (_EmployeeID != 0)
 {
 DB.NW.DataContext dc = new DB.NW.DataContext();
 DB.NW.Employee employee = dc.Employees.Single(em => em.EmployeeID == _EmployeeID);
 _Version = employee.Version;
 tb_FirstName.Text = employee.FirstName;
 tb_LastName.Text = employee.LastName;
 tb_HomePhone.Text = employee.HomePhone;
 }
 }
 base.OnLoad(e);
}

I'm going to assume you seen the reading of a single record from a LINQ to SQL DataContext, if not Scott Guthrie has a good introduction to LINQ to SQL you should read first. I'm also using the null coalescing operator, if that's new to you then again, Scott covered that too.

Notice here that I'm storing two values to local variables (ie ViewState). The _EmployeeID and the _Version. The version field is a row TimeStamp field I've added to the Employee table to facilitate concurrency checking on updates. I'll talk more about this later in this post.

Now for the good stuff. Here's the Save method...

protected void Save(object source, EventArgs e)
{
 DB.NW.DataContext dc = new DB.NW.DataContext();
 DB.NW.Employee employee = new DB.NW.Employee();
 if (_EmployeeID == 0)
 {
 employee.FirstName = tb_FirstName.Text;
 employee.LastName = tb_LastName.Text;
 employee.HomePhone = tb_HomePhone.Text;
 dc.Employees.Add(employee);
 }
 else
 {
 employee.EmployeeID = _EmployeeID;
 employee.Version = _Version;
 dc.Employees.Attach(employee);
 employee.FirstName = tb_FirstName.Text;
 employee.LastName = tb_LastName.Text;
 employee.HomePhone = tb_HomePhone.Text;
 }
 try
 {
 dc.SubmitChanges();
 Response.Redirect("~/Employees.aspx");
 }
 catch (Exception ex)
 {
 lbl_Message.Text = ex.Message;
 }
}

Let's break it down.

When adding a new employee record.

Start by creating a DataContext and a new Employee object. Then set the properties based on the form control values and "Add" the employee object to the DataContext's Employees table. Finish with a SubmitChanges().

When updating things get a bit more interesting.

Start by setting the id and version properties of the new employee object and then "Attach" the it to the DataContext's Employees table. After it is attached, move the form control values to the employee object and finish by calling SubmitChanges().

It is important to understand why this sequence of steps is required for an update. The attach step is the key and it relates to how the DataContext tracks changes to objects. The "Attach" tells the DataContext that we have this employee object with an id of _EmployeeID and a row version timestamp of _Version and we want you to know about it and track any further changes so you can save them. When calling SubmitChanges(), the DataContext will create it's update statement and only include fields that changed after the object was attached.

Variations on the Update

While the code above is a complete workable approach, there are some assumptions and pre-requisites that led to it. First, the table has a timestamp field to track the row version for concurrency checking. If your table doesn't have one, in most cases I'd suggest adding it. If that's not possible then you have a few options, all of which involve more work on your part.

The easiest option is to turn off concurrency checking by changing the "update check" attribute for each field and setting it to "never". By default, LINQ to SQL sets the attribute to "always". This will allow the above code above to continue to work (just remove the lines dealing with the Version field). But removing concurrency checking isn't a good idea unless you intend to handle it yourself by adding your own code for it to the update process above.

What you shouldn't do is simply re-retrieve the employee record and change its properties. If you are handling the currency checking on your own via a property by property compare or a last updated date time value, you may think you are covered. The problem is that you are either trying to ignore concurrency checking or you are doing your own concurrency checking while still allowing LINQ to SQL to do it's concurrency checks as well so you may get a LINQ to SQL concurrency error if two users try to update a record a the same time, even if you are doing some amount of your own checking. It the end you either need to use LINQ to SQL's concurrency process or to turn it off.

Here's a bit more food for thought on this subject. Below are approaches to updates and the SQL statements generated by LINQ to SQL.

First, the code from above that makes use of a Version field and LINQ to SQL's concurrency checking...

DB.NW.Employee employee = new DB.NW.Employee();
employee.EmployeeID = _EmployeeID;
employee.Version = _Version;
dc.Employees.Attach(employee);
employee.FirstName = tb_FirstName.Text;
employee.LastName = tb_LastName.Text;
employee.HomePhone = tb_HomePhone.Text;
dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]
SET [LastName] = @p2, [FirstName] = @p3, [HomePhone] = @p4
WHERE ([EmployeeID] = @p0) AND ([Version] = @p1)

Turning off update checks on your fields and removing the Version field leaves you vulnerable to concurrency issues, but here's the code...

DB.NW.Employee employee = new DB.NW.Employee();
employee.EmployeeID = _EmployeeID;
dc.Employees.Attach(employee);
employee.FirstName = tb_FirstName.Text;
employee.LastName = tb_LastName.Text;
employee.HomePhone = tb_HomePhone.Text;
dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]
SET [LastName] = @p1, [FirstName] = @p2, [HomePhone] = @p3
WHERE [EmployeeID] = @p0

And last, if you leave the Update Check on and re-retrieve the employee record the code ...

employee = dc.Employees.Single(em => em.EmployeeID == _EmployeeID);
employee.FirstName = tb_FirstName.Text;
employee.LastName = tb_LastName.Text;
employee.HomePhone = tb_HomePhone.Text;
dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]
SET [HomePhone] = @p15
WHERE ([EmployeeID] = @p0) AND ([LastName] = @p1) AND ([FirstName] = @p2) 
AND ([Title] = @p3) AND ([TitleOfCourtesy] = @p4) AND ([BirthDate] = @p5) 
AND ([HireDate] = @p6) AND ([Address] = @p7) AND ([City] = @p8) 
AND ([Region] IS NULL) AND ([PostalCode] = @p9) AND ([Country] = @p10) 
AND ([HomePhone] = @p11) AND ([Extension] = @p12) AND ([ReportsTo] = @p13) 
AND ([PhotoPath] = @p14)

As you can see in the last of the three update statements, LINQ to SQL is sending every field as part of the update statement's where clause. I'll also point out here that in this case, LINQ to SQL does know that the FirstName and LastName fields were not changed to new values and therefore didn't include those in the update.

A Note on Change Tracking

This is a more advanced and situational concept, but one of the interesting possibilities that LINQ to SQL enables is the ability to build change tracking into the SubmitChanges() process. The basic idea is that any time you make changes to the database, you first go through the list of changes being made and record them in a generic change log table. I'll be writing about this idea in a later post, but for now, I'll throw out a warning that may effect how you handle updates.

When using the model I suggest at the beginning of this post, the DataContext doesn't know the original values of the three fields (FirstName, LastName and HomePhone) that are being set. As far as the DataContext is aware, the original values are null so, even if nothing is really changing, the fields will be added to the change set. This means when you go through the change set during SubmitChanges() the original values will be empty. So, if you want to really know, at a field level what is truly changing, you'll need to take a different approach that what is shown above.

Here I've stored the original values for all fields that can be updated and I set the employee object to those values before attaching it to the Employees table.

DB.NW.Employee employee = new DB.NW.Employee();
employee.EmployeeID = _EmployeeID;
employee.Version = _Version;
employee.FirstName = _FirstName;
employee.LastName = _LastName;
employee.HomePhone = _HomePhone;
dc.Employees.Attach(employee);
employee.FirstName = tb_FirstName.Text;
employee.LastName = tb_LastName.Text;
employee.HomePhone = tb_HomePhone.Text;
dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]
SET [HomePhone] = @p2
WHERE ([EmployeeID] = @p0) AND ([Version] = @p1)

In this case, LINQ to SQL knows the original values for the properties and as in the third example I gave earlier it generates the update set statement with the HomePhone, which was the only field that changed. During the SubmitChanges, if you look into the ChangeSet and retrieve the list of changed properties, you'll find that only the HomePhone is given and it has the original and new values. This will enable you to record the changes in your generic change log table.

So, in order to do both change tracking at the field level and concurrency checking in a disconnected environment such as ASP.Net, you need to be able to create your employee object and set all the properties that you may be changing to their original values before you attach and then update those properties.

I'll be writing a post later that goes into more detail and presents the pros and cons on a few different approaches to tracking original values for objects that you will be updating.

Published Saturday, September 29, 2007 4:37 PM by bschooley
Filed under: ,

Comments

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Sunday, September 30, 2007 4:02 AM by Rick Strahl

I've been mucking with these issues for a while as well. I wonder though - If you're writing code with Linq to SQL like above I don't really see a reason to use disconnected entities. Even if you were using a business layer/middle tier it seems it would be more efficient and easier to track operations by running connected. Only time discconnected is important if you - well, really disconnect.

If you work with timestamps in your table the latter assignments shouldn't be necessary by the way, at least not in my experience. I use a generic routine that looks like this:

       /// <summary>

       /// Saves a disconnected entity object

       /// </summary>

       /// <param name="entity"></param>

       /// <returns></returns>

       public virtual bool Save(TEntity entity)        

       {

           if (this.AutoValidate && !this.Validate())

               return false;

           // *** In connected mode any Save operation causes

           // *** all changes to be submitted.

           if (this.Options.TrackingMode == TrackingModes.Connected || entity == null)

           {

               try

               {

                   return this.SubmitChanges();

               }

               catch (Exception ex)

               {

                   this.SetError(ex);                  

               }

               return false;

           }

           if (entity == null)

               entity = this.Entity;

           using (TContext context = this.CreateContext())

           {

               try

               {

                   // *** BIG assumption here: _version field and using Reflection - yuk!

                   //     can't see another way to figure out whether we're dealing with

                   //     a new entity or not.

                   object tstamp = entity.GetType().GetProperty(this.TableInfo.VersionField).GetValue(entity,null);                    

                   Table<TEntity> table = context.GetTable( typeof(TEntity)) as Table<TEntity>;

                   if (tstamp == null)

                       table.Add(entity);

                   else

                       table.Attach(entity, true);

                   context.SubmitChanges();

               }

               catch (Exception ex)

               {

                   this.SetError(ex);

                   return false;

               }

               context.Dispose();

           }

           return true;            

       }

And that works as long as there's a timestamp. Note the true on the Attach method which is the key to make this work, but it currently only works with time stamps.

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Sunday, September 30, 2007 9:59 AM by bschooley

Hello Rick,

>>>>I've been mucking with these issues for a while as well. I wonder though - If you're writing code with Linq to SQL like above I don't really see a reason to use disconnected entities. Even if you were using a business layer/middle tier it seems it would be more efficient and easier to track operations by running connected. Only time discconnected is important if you - well, really disconnect.<<<<

You're always running disconnected when dealing with updates via ASP.Net. When you present the data to a user you are grabbing the data (along with the timestamp) and sending it to them. They may sit on that data for an extended period of time before they submit their changes. Once they do you need to be able to send it back into the database but need to follow the disconnected model in order to properly handle the concurrency check.

>>>>If you work with timestamps in your table the latter assignments shouldn't be necessary by the way, at least not in my experience. I use a generic routine that looks like this... <<<<

I probably should have written a note about the asModified parameter on the Attach method you're using. The placement of my Attach without the asModified (it's default value is false when not supplied) is important because I'm creating my object and setting it's key and version which aren't changed and then attaching it. Subsequently I set the properties which are changing and allow the natural onPropertyChanging processes to run their course.

Technically you're right, I could have set those properties before the attach and used the asModified=true parameter. The reason I did not is more obvious in my final code example for the change tracking note where I attach the object with the original property values and then after the attach I set the new values. Besides the benefits for change tracking that I noted in the post, this allows LINQ to SQL to create the most effecient UPDATE statement by only issueing SET's for fields that really changed. If the user hadn't changed any values but hit save anyway, LINQ to SQL is able to skip the save all together.

While situationally your code undoubtedly works for you, there are a few reasons why I wouldn't recommend that approach. First, it hides from view two distinctly different operations (add and attach). Second, it doesn't leave room for attaching the object with original values before setting the new values. And third, it issues the SubmitChanges on each Save. This breaks the transactional benefits where you change multiple objects before issuing the SubmitChanges which can then all run within a single transaction.

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Wednesday, May 28, 2008 1:42 PM by Thomas Jespersen

You don't have to work with disconnected objectes i ASP.NET. When the user posts back changes, then the first thing you do is to featch the latest version of the object form the database (using the id, from the viewstate).

Then you verify that the timestamp of the fetched object equals the time stamp from your viewstate. If they do, you are set to go. If they don't you can trhow an exception right there, because you know that the update will fail.

While theis requires an extra roundtrip to the server, this can be a very good aproch if you entity object is more complex. E.g. its an Aggregate which contains other objectes. Also by using this aproach you don't have to send every property to the client, which saves you from checking that the data has not been tampered with (say a usere changes the customer id of an order... how would you detect that)?

: Thomas

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Thursday, September 22, 2011 4:17 AM by varunmaggo

Nice one! This really saved me alot of time.

Bestest

Varun Maggo

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Sunday, September 30, 2012 7:56 AM by Connie

Hi Jesse,Thanks for all the great WP7 posts!  They've helped me out quite a bit elcapielsy with regard to MVVM Light.So, while I'm excited about the advent of SQL CE in Mango, I'm a bit curious as to what you recommend that we do until then in terms of both best practices and pragmatism. I'm posing my question with the following assumptions in place (please correct me if you disagree):(1) Program against an interface for local data storage(2) For the time being, provide concrete implementation using X, Y, and/or Z(2) When Mango comes out, switch concrete implementation to SQL CESo, for the time being, should we rely on Isolated Storage, SQLite for WP7 (by Dan Ciprian Ardelean), and/or Sterling OODB (by Jeremy Likness)?  If it's a combination of two or more, then which scenarios go with which local storage technology? And, last but not least, once Mango is released and we have SQL CE, should we still leverage Isolated Storage for anything?Thanks again for all the great info on what's coming Mango!  Looking forward to your hearing your take on what to do about local storage in the interim.

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Friday, February 22, 2013 12:01 AM by Renteria

You made some decent points there. I looked on the internet for more information about the

issue and found most individuals will go along

with your views on this website.

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Wednesday, April 24, 2013 3:05 AM by Cage

z, którym drzewiej płynęła strumień,

srebrzył się drgającymi rybami, Adam chwytającymi powietrze spazmatycznymi ruchami skrzeli.

Dotychczasowy w tamtym miejscu karaski, leszcze,

jazie, poniekąd kilka jesiotrów jak i również zabłą.

# re: LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

Friday, May 3, 2013 2:37 AM by Hill

Asking questions are genuinely fastidious thing if

you are not understanding anything fully, except this post presents

nice understanding even.