When learning LINQ-to-SQL, it’s not immediately obvious how to do an update. Querying is easy, and there are methods for inserting and deleting. Updating usually occurs by modifying an object already known to the DataContext and then calling SubmitChanges on the context.

var product = (from p in dataContext.Products
               where p.ProductID == 1
               select p).Single();

product.Name = "Richard’s product";

dataContext.SubmitChanges();

It’s nice to see that MSDN documentation actually addresses the obvious arising question:

Q. Can I update table data without first querying the database?

A. Although LINQ to SQL does not have set-based update commands, you can use either of the following techniques to update without first querying:

  • Use ExecuteCommand to send SQL code.
  • Create a new instance of the object and initialize all the current values (fields) that affect the update. Then attach the object to the DataContext by using Attach and modify the field you want to change.

If you ask me, using an ExecuteCommand defeats the object of LINQ-to-SQL. After all, we’re using it as a data-access-layer to generate entities in code based on the data schema. The last thing we want to have to do is start writing strings of SQL.

So that’s out.

I’m So Attached

The second option is to use the Attach method on the table we’re updating. I’ve tried to use the Attach method before, but it didn’t work.

To see what I mean, try this in LINQPad (using the AdventureWorks database from CodePlex):

var product = new Product();
product.ProductID = 1;
product.Name = "Richard’s product";

Products.Attach(product);

var changeSet = GetChangeSet();

changeSet.Dump();

You’ll see that there are no changes to make, so no updates are made.

Now, move the line of code that does the update after the call to Attach, so as to attach the product first before updating the field:

var product = new Product();
product.ProductID = 1;

Products.Attach(product);

//Notice we’re doing this laterproduct.Name = "Richard’s product";

var changeSet = GetChangeSet();

changeSet.Dump();

The changeset now shows that one update is ready to be made. Unfortunately, if you call SubmitChanges, you get an exception:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The MSDN documentation isn’t so clear here. What do we need to set to get it to work?

The SQL being sent to the database is instructive:

UPDATE [Production].[Product]
SET [Name] = @p9
WHERE ([ProductID] = @p0) AND ([Name] IS NULL)
AND ([ProductNumber] IS NULL)
AND (NOT ([MakeFlag] = 1))
AND (NOT ([FinishedGoodsFlag] = 1))
AND ([Color] IS NULL)
AND ([SafetyStockLevel] = @p1)
AND ([ReorderPoint] = @p2)
AND ([StandardCost] = @p3)
AND ([ListPrice] = @p4)
AND ([Size] IS NULL)
AND ([SizeUnitMeasureCode] IS NULL)
AND ([WeightUnitMeasureCode] IS NULL)
AND ([Weight] IS NULL)
AND ([DaysToManufacture] = @p5)
AND ([ProductLine] IS NULL)
AND ([Class] IS NULL)
AND ([Style] IS NULL)
AND ([ProductSubcategoryID] IS NULL)
AND ([ProductModelID] IS NULL)
AND ([SellStartDate] = @p6)
AND ([SellEndDate] IS NULL)
AND ([DiscontinuedDate] IS NULL)
AND ([rowguid] = @p7)
AND ([ModifiedDate] = @p8)
– @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
– @p1: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [0]
– @p2: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [0]
– @p3: Input Decimal (Size = 0; Prec = 29; Scale = 4) [0]
– @p4: Input Decimal (Size = 0; Prec = 29; Scale = 4) [0]
– @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

The problem is now that LINQ-to-SQL tables automatically use optimistic concurrency, and for some reason (maybe only in LINQPad) the SellStartDate is not set, and neither are the rowguid or ModifiedDate fields. In any case, either we have to turn off optimistic concurrency, or set all the field values ourselves.

Using Optimistic Concurrency with Updates

Unfortunately, by default all fields are used for optimistic concurrency, so you have to set values for all fields. If you were using ASP.Net you could keep a copy of the variables in the session state or something, but at least it saves you a round trip to the database.

Here’s the code that actually does perform an update:

var product = new Product();
product.ProductID = 1;
product.Name = "Adjustable Race";
product.ProductNumber = "AR-5381";
product.MakeFlag = false;
product.FinishedGoodsFlag = false;
product.Color = null;
product.SafetyStockLevel = 1000;
product.ReorderPoint = 750;
product.StandardCost = 0.0000M;
product.ListPrice = 0.0000M;
product.Size = null;
product.SizeUnitMeasureCode = null;
product.WeightUnitMeasureCode = null;
product.Weight = null;
product.DaysToManufacture = 0;
product.ProductLine = null;
product.Class = null;
product.Style = null;
product.ProductSubcategoryID = null;
product.ProductModelID = null;
product.SellStartDate = DateTime.Parse("6/1/1998 12:00:00 AM");
product.SellEndDate = null;
product.DiscontinuedDate = null;
product.Rowguid = new Guid("694215b7-08f7-4c0d-acb1-d734ba44c0c8");
product.ModifiedDate = DateTime.Parse("3/11/2004 10:01:36.827 AM");

Products.Attach(product);

// Make the change here
product.Name = "Richard’s product";

SubmitChanges();

So you can avoid two trips to the database by doing the update manually and using the Attach method on the table you are updating.

Mind you, I’m not sure that’s worth it.

If you want to know more about updating LINQ-to-SQL objects with the DataContext, I recommend Pro LINQ: Language Integrated Query in C# 2008. It’s got 6 chapters just about LINQ-to-SQL, the DataContext, and concurrency issues.

P.S. In case you’re interested, I generated some of that code in LINQPad using the following code (it doesn’t work for dates or Guids, before anyone complains):

var product = (from p inProducts

wherep.ProductID == 1

selectp).Single();

var fields = from field inproduct.GetType().GetFields()

select new{

Name = field.Name,

Value = field.GetValue(product),

TypeName = field.FieldType.Name };

foreach (var field infields)

Console.WriteLine("product.{0} = {1};", field.Name,

field.TypeName == "String"? "\""+

field.Value + "\"":

field.Value ?? "null");