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.