LINQ to SQL Updating in ASP.NET - The Right Way

Update (6/7/2008): I have posted my thoughts on how this can be done in a more n-tier based way here.

Click here to download the source for these examples. This is a link to a Google Code repository.  Use subversion to check it out.

Like many folks who have been creating web applications on the ASP.NET platform for a while, the glitz and glamour surrounding Language Integrated Query (LINQ) intrigued and frightened me at the same time.  All the demos seemed to imply that life was easier and clearer in the new world.  Don't believe the hype. 

Don't get me wrong, LINQ is great if you understand the right way to use it and our teams at Pariveda are really getting some great traction out of LINQ to SQL, but you definitely have to approach it differently than most of the demos seem to imply.  The QUERY part of LINQ to SQL is straightforward and extremely powerful; the create, update and delete support in the language is what takes some getting used to.

First off, it's good to understand the model that is used for LINQ to SQL Updates.  The entities that are passed around with LINQ to SQL are lightweight and do not store changed state information. The only things the entities do is store data and raise events when their properties change to other watching objects such as the DataContext-- and that is where folks get confused when working with LINQ in ASP.NET.

Almost ALL applications these days work in a disconnected state.  Meaning there are process/memory differences between when and where data is initially RETRIEVED and when and where it is eventually UPDATED.  Herein lies the problem with most of the demos that show LINQ in action.

ASP.NET web applications are and should remain stateless from an architectural implementation.  We have ways of "fooling" developers into believing that their pages are somehow living in memory using things like ViewState, but anyone who has worked in the tool long enough knows that ViewState is serialization/deserialization trickery to lull the developer into a stateful sense of well being.  I love me some ViewState, but you have to understand it to use it wisely.

So, what's the problem with ASP.NET and LINQ.  When you perform an update with LINQ, the entity you are updating must be watched when the properties are updated to their new values. 

Say, I have a page that displays a customer record:

On the SelectedIndexChanged of the ListBox, I would simply write some code that filled in the values in the TextBoxes like so:

protected void CustomerListBox_SelectedIndexChanged(object sender, EventArgs e)

{

    using (AdventureWorksDataContext db = new AdventureWorksDataContext())

    {

        Customer customer = db.Customers.Single(c => c.CustomerID ==

            int.Parse(CustomerListBox.SelectedValue));

        NameTextBox.Text = customer.CompanyName;

        EmailTextBox.Text = customer.EmailAddress;

        FirstNameTextBox.Text = customer.FirstName;

        LastNameTextBox.Text = customer.LastName;

        IsActiveCheckBox.Checked = customer.IsActive;

    }

}

We think all is well with the world.  Then we proceed to Update the Customer object by hooking into the Save Button's click event like so:

protected void SaveButton_Click(object sender, EventArgs e)

{

    using (AdventureWorksDataContext db = new AdventureWorksDataContext())

    {

        Customer customer = new Customer

        {

            CustomerID = int.Parse(CustomerListBox.SelectedValue)

        };

        db.Customers.Attach(customer);

        customer.CompanyName = NameTextBox.Text;

        customer.EmailAddress = EmailTextBox.Text;

        customer.FirstName = FirstNameTextBox.Text;

        customer.LastName = LastNameTextBox.Text;

        customer.IsActive = IsActiveCheckBox.Checked;

        db.SubmitChanges();

    }

}

And we get a "row not found or changed" error?  Why is this?  Because the datacontext uses concurrency checking to ensure that the data we originally retrieved has not changed between the time we retrieved it and the time we are attempting to update it.  Let's take a look at the SQL that was generated by the DataContext that caused the error:

UPDATE [SalesLT].[Customer]
SET [FirstName] = @p0, [LastName] = @p1, [CompanyName] = @p2, [EmailAddress] = @p3WHERE 0 = 1
-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Orlando]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Gee]
-- @p2: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [A Bike StoreX]
-- @p3: Input NVarChar (Size = 28; Prec = 0; Scale = 0) [orlando0@adventure-works.com]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Notice, the WHERE 0=1 part?  What's up with that?

The best way to start off solving this is to create a timestamp column in your table and hold on to this value in ViewState so that when you perform your Attach and Update, you can vouch for the fact that the data hasn't changed since the last time you retrieved it.  We'll call it the column "Version".

Next, we'll create a ViewState-backed property that will hold onto the value of the currently selected version:

Binary SelectedCustomerVersion

{

    get { return (Binary)ViewState["SelectedCustomerVersion"]; }

    set { ViewState["SelectedCustomerVersion"] = value; }

}

We'll set the Version property in our SelectedIndexChanged event of our ListBox like so:

protected void CustomerListBox_SelectedIndexChanged(object sender, EventArgs e)

{

    using (AdventureWorksDataContext db = new AdventureWorksDataContext())

    {

        Customer customer = db.Customers.Single(c => c.CustomerID ==

            int.Parse(CustomerListBox.SelectedValue));

        NameTextBox.Text = customer.CompanyName;

        EmailTextBox.Text = customer.EmailAddress;

        FirstNameTextBox.Text = customer.FirstName;

        LastNameTextBox.Text = customer.LastName;

        IsActiveCheckBox.Checked = customer.IsActive;

        SelectedCustomerVersion = customer.Version;

    }

}

Finally, in our update code, we will be sure and set the Version value of our Customer entity before we attach to the data context.

protected void SaveButton_Click(object sender, EventArgs e)

{

    using (AdventureWorksDataContext db = new AdventureWorksDataContext())

    {

        Customer customer = new Customer

        {

            CustomerID = int.Parse(CustomerListBox.SelectedValue),

            Version = SelectedCustomerVersion

        };

        db.Customers.Attach(customer);

        customer.CompanyName = NameTextBox.Text;

        customer.EmailAddress = EmailTextBox.Text;

        customer.FirstName = FirstNameTextBox.Text;

        customer.LastName = LastNameTextBox.Text;

        customer.IsActive = IsActiveCheckBox.Checked;

        db.SubmitChanges();

    }

}

And this works... MOST of the time.  You can be deceived into thinking you have things working until you understand what is actually occurring.  When you attach your customer entity to your datacontext, you are basically promising that the values you plan to change in your entity while your data context watches are ALREADY SET to the original values they were in the database.  The reason why this is important for LINQ is that your Customer entity will only alert the data context that something has changed if the value the property is changing FROM is different.  So how would you see this problem manifest itself? It will occur when you are setting a property back to the default value that it would already have when a new entity is created-- boolean values are the easiest ways to see the issue.

If we we set the IsActive property from true to false, our current code won't work because the default value of a boolean is already false.  Let's look at what the SQL would look like if the ONLY thing we changed in the entity was the IsActive checkbox going from checked to unchecked (true to false).

UPDATE [SalesLT].[Customer]
SET [FirstName] = @p2, [LastName] = @p3, [CompanyName] = @p4, [EmailAddress] = @p5
WHERE ([CustomerID] = @p0) AND ([Version] = @p1)

SELECT [t1].[Version]
FROM [SalesLT].[Customer] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerID] = @p6)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- @p2: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Brian]
-- @p3: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Gee]
-- @p4: Input NVarChar (Size = 12; Prec = 0; Scale = 0) [A Bike Store]
-- @p5: Input NVarChar (Size = 28; Prec = 0; Scale = 0) [orlando0@adventure-works.com]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Notice, that even though the ONLY column we changed is the IsActive value from true to false, it was the only column that wasn't attempted to be updated by the database.  Let's examine why. 

When you attach an entity to the datacontext, it is basically hooking up to be alerted everytime a property changes.   Since we just created an empty customer from scratch and only set the CustomerID and Version properties on the entity, all of the other property values are null.  When we set CompanyName, it alerts that there was a change because we changed the property from null to a value, and the same for the other columns... except for IsActive.  The CURRENT value of IsActive is false and then we attempt to set the value of the property to false.  No change event is raised because the property isn't being changed and therefore the database is not updated during the SubmitChanges call.

So, the last piece of the puzzle is coming up with a way to hold on to the original values of the customer entity so that before we attach the customer entity we set all the properties to their new values, the old values are already set.  This is the part that had my head spinning for a while.  At first, I tried putting the Customer entity directly into ViewState but it complained about a host of serialization problems-- you can try making it happen and see for yourself the problems that occur.

The way I finally was able to get serialization of a LINQ entity to work was to set the dbml file to the Serialization Mode of Unidirectional.  You can do this by clicking in a blank area of the designer and then opening up the properties window.

Next, I created an OriginalCustomer property to store the object.

Customer OriginalCustomer { get; set; }

Finally, I overrode the LoadViewState and SaveViewState methods of the page so that I could persist the deserialized string of  the OriginalCustomer property to ViewState.

protected override void LoadViewState(object savedState)

{

    object[] allStates = (object[])savedState;

    base.LoadViewState(allStates[0]);

    OriginalCustomer = LinqEntityUtil.Deserialize<Customer>((string)allStates[1]);

}

protected override object SaveViewState()

{

    object baseState = base.SaveViewState();

    object[] allStates = new object[2];

    allStates[0] = baseState;

    allStates[1] = LinqEntityUtil.Serialize<Customer>(OriginalCustomer);

    return allStates;

}

Here's the utility class that handles the deserialization of LINQ Entities:

public static class LinqEntityUtil

{

    /// <summary>

    /// Serializes a LINQ to SQL Entity to a WCF-Serialized

    /// string using the DataContractSerializer

    /// </summary>

    /// <typeparam name="T">Type of the LINQ Entity to be serialized</typeparam>

    /// <param name="linqEntity">LINQ Entity object to be serialized</param>

    /// <returns>Serialized string</returns>

    public static string Serialize<T>(T linqEntity)

    {

        DataContractSerializer serializer = new DataContractSerializer(typeof(T));

        StringWriter writer = new StringWriter();

        XmlTextWriter xmlWriter = new XmlTextWriter(writer);

        serializer.WriteObject(xmlWriter, linqEntity);

        writer.Flush();

        return writer.ToString();

    }

 

    /// <summary>

    /// Deserializes from a string back to a LINQ to SQL Entity

    /// object using the DataContractSerializer

    /// </summary>

    /// <typeparam name="T">Type of the LINQ Entity to be serialized</typeparam>

    /// <param name="xml">Serialized string to deserialize</param>

    /// <returns>LINQ Entity object</returns>

    public static T Deserialize<T>(string xml)

    {

        DataContractSerializer serializer = new DataContractSerializer(typeof(T));

        StringReader reader = new StringReader(xml);

        XmlTextReader xmlReader = new XmlTextReader(reader);

        T linqEntity = (T)serializer.ReadObject(xmlReader);

        return linqEntity;

    }

}

Now, with my OriginalCustomer serialization in place, I could hold on to the original customer entity and attach it back to the datacontext when I was ready to save.

protected void SaveButton_Click(object sender, EventArgs e)

{

    using (AdventureWorksDataContext db = new AdventureWorksDataContext())

    {

        db.Customers.Attach(OriginalCustomer);

        OriginalCustomer.CompanyName = NameTextBox.Text;

        OriginalCustomer.EmailAddress = EmailTextBox.Text;

        OriginalCustomer.FirstName = FirstNameTextBox.Text;

        OriginalCustomer.LastName = LastNameTextBox.Text;

        OriginalCustomer.IsActive = IsActiveCheckBox.Checked;

        db.SubmitChanges();

    }

}

With this in place, I can make changes to IsActive where I set it from true to false and the data context "sees" the changes appropriately.  Here is the much more optimized SQL that occurs, when I change just the IsActive field.

UPDATE [SalesLT].[Customer]
SET [IsActive] = @p2
WHERE ([CustomerID] = @p0) AND ([Version] = @p1)

SELECT [t1].[Version]
FROM [SalesLT].[Customer] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerID] = @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- @p2: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Hopefully this helps some of you struggling with similar issues.  The key thing when using attach is that you want all of the properties that you plan to change to be set to their original values before you attach it to the datacontext and ViewState serialization is the easiest way to do that in ASP.NET.

Click here to download the source for these examples.

Technorati Tags: LINQ,ASP.NET,Serialization,WCF