How to Update Data with LINQ-to-SQL

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");

« Previous Post

Adam Nofsinger
26 Feb, 2008

Wow, that really sucks. (The fact that you can’t just do something like:

update c in Customer
where c.CustomerID = 12
set c.Name = “Bob WeHadaBabyItsABoy”
DariaK
3 Mar, 2008

Here is the tool for database synchronization that supports Linq to Sql object model:

http://perpetuumsoft.com/Product.aspx?lang=en&pid=55

I suppose It is worth to try!
Tomas Jecha
8 Mar, 2008

Nice article, thanks.
Dave
17 Mar, 2008

I use sprocs for ‘arbitrary updates’, where I’m updating records I haven’t queried and don’t need to query.

Its not ideal, but its simple, can be called through the linq datacontext, doesn’t require a round trip, doesn’t result in SQL strings in code, and doesn’t require a clumsy hack where you are trying to build the object to update it.
Bruno Kenj
1 May, 2008

Pessoa p2 = new Pessoa();
p2.Id = 1;
p2.Nome = “Teste” + DateTime.Now.ToString();

DbContext.Pessoas.Attach(p2);
DbContext.Refresh(RefreshMode.KeepCurrentValues, p2);
DbContext.SubmitChanges();

this way, only chances are updated.
Richard Bushnell
1 May, 2008

@Bruno:

That looks interesting, but doesn’t it really do the same as doing a select before an update?
TUAN
6 May, 2008

I had spend 2 hours to find the way to use Update in LINQ, and now, I want to thanks u about this article
—-
Nice article, thanks u alot!
Neal Walters
28 May, 2008

How does your changeset.Dump() work?
Thanks,
Neal
Richard Bushnell
29 May, 2008

Hi Neal,

Changeset.Dump() is something that only works in LinqPad.

– The changeset is generated from the DataContext, but in LinqPad, you don’t reference the DataContext, because you are actually inside it.

- Dump() is a LinqPad method defined as an extension method on Object. It is LinqPad specific.

If you want more information about LinqPad, you can contact the authors at http://www.linqpad.net. When I contacted them, they were very helpful. I’m sure they’d let you know how it works.

HTH,
Richard
Mickey Mouse
27 Jun, 2008

With NHibernate you just create an instance of the entity, change the fields desired then call the Save method, done!
Christian Zangl
1 Aug, 2008

If you go to your database.dbml and change the “Update Check” property on the Product fields to “Never” then your code will work and you’ll get the SQL you wanted:

var product = new Product();
product.ProductID = 1;
Products.Attach(product);
product.Name = “Richard’s product”;
SubmitChanges();
Bjarne Pedersen
6 Oct, 2008

Well, why not just use:

if(val.recordId == 0){
db.GrandPrixValues.InsertOnSubmit(val);
}
db.SubmitChanges();

Thats working and way more easy!
anil
25 Oct, 2008

Hi I have tested with following code but didn’t worked

Pessoa p2 = new Pessoa();
p2.Id = 1;
p2.Nome = “Teste” + DateTime.Now.ToString();

DbContext.Pessoas.Attach(p2);
DbContext.Refresh(RefreshMode.KeepCurrentValues, p2);
DbContext.SubmitChanges();

this way, only chances are updated.

Can someone let me know whether its possible
Martin Montgomery
28 Oct, 2008

Just a quick piece of code to do a general update on all records

private Product UpdateSomething(Product product)
{
product.value = 99;
return product;
}

public void UpdateAll()
{
var list = (from product in DataContext.GetTable()
select UpdateSomething(product)).ToList();

DataContext.SubmitChanges();
}

We can also include a where clause to filter those products being updated
Param Iyer
23 Nov, 2008

I could not find a solution to this problem for almost 5 hours before stumbling down here and writing the below piece of code…

Pessoa p2 = new Pessoa();
p2.Id = 1;
p2.Nome = “Teste” + DateTime.Now.ToString();

DbContext.Pessoas.Attach(p2);
DbContext.Refresh(RefreshMode.KeepCurrentValues, p2);
DbContext.SubmitChanges();

this thing does WORK
Damian
13 Jan, 2009

Pessoa p2 = new Pessoa();
p2.Id = 1;
p2.Nome = “Teste” + DateTime.Now.ToString();

DbContext.Pessoas.Attach(p2);
DbContext.Refresh(RefreshMode.KeepCurrentValues, p2);
DbContext.SubmitChanges();

It’s works.
Drako
31 Mar, 2009

This is my class DBMaintenance – all works fine

public object Insert(object item)
{
using (TransactionScope ts = new TransactionScope())
{
ITable itbl = DataContext.GetTable(item.GetType());
itbl.InsertOnSubmit(item);
itbl.Context.SubmitChanges();
ts.Complete();
}
return item;
}
public object Delete(object item)
{
using (TransactionScope ts = new TransactionScope())
{
ITable itbl = DataContext.GetTable(item.GetType());
itbl.DeleteOnSubmit(item);
itbl.Context.SubmitChanges();
ts.Complete();
}
return item;
}

public object Update(object item)
{
using (TransactionScope ts = new TransactionScope())
{
ITable itbl = DataContext.GetTable(item.GetType());
itbl.Attach(item);
itbl.Context.Refresh(RefreshMode.KeepCurrentValues, item);
itbl.Context.SubmitChanges();
ts.Complete();
}
return item;
}
Amr Ellafi
12 Jul, 2009

@Bruno: your solution works !
Mr. A
15 Jul, 2009

For all these answers, I keep getting Cannot attach an entity that already exists.

Somebody want to explain why an update would cause that error and how to correct it?
saman
5 Sep, 2009

sorry, i have a table that name is “table1″
and it has 3 fields, id,name and family and id is primary key
when i use this code to update table, i have this error
my code:
int SelectedId = 5;
var Query = (from P in bank.Table1
where P.id == SelectedId
select P).Single();
Queryid = 0;
Query.name = “change”;
Query.famil = “change”;
bank.SubmitChanges();
my error:
Can’t perform Create, Update or Delete operations on ‘Table(Table1)’ because it has no primary key.
Pure Krome
26 Oct, 2009

Folks, Christian Zangl post above is the correct answer IMO.

By default, Linq to Sql uses Optimistic Concurrency (aka. O.C.). Everyone knows that and we’re all not disputing that.

So _BY DEFINITION_ this means that we _cannot_ update our object UNLESS we know it’s the same version in the database – otherwise we need to throw an exception. That’s what O.C. is all about.

So -> Linq to Sql does this in two ways.
1) If you have a TIMESTAMP field in the table, then send that across the wire along with the Primary Key and make sure that the current object’s TIMESTAMP field (that is about to be updated) is the same as the DB’s.

-or-

2) No timestamp? then we need to check -each- field (which is what is happening in Dinesh’s example code, above).

So … maybe you don’t want O.C. then?! If you _want_ to make sure that the data you’re about to update IS not more recent that the version in the code, then yes .. u need O.C. Otherwise, don’t do Optimistic Concurrency and therefore all these hacks to get around it.

To not use O.C., make sure each field in the table that is being updated, the ‘Update Status’ field is set to NEVER. The default is Always … so switch it over to NEVER.

As is said, Christian Zangl has said it perfectly right, above.

Good Luck!
Pure Krome
26 Oct, 2009

Oops -> i have an error in the following sentence above.

“which is what is happening in Dinesh’s example code, above).”

should read as

“which is what is happening in Richard Bushnell’s example code, above).”
Real programmer
10 Dec, 2009

LINQ just sucks, don’t use it! It’s slow and inflexible.

It’s a trap to lure people away from real programming into M$ proprietary crap.

Stick to SQL and you can work on every platform, not just M$
Ved
13 Mar, 2010

LINQ does not suck , you just do not know it.

Make sure to do db.Table.Single(query) only then update works.

if you do db.Table.First(query) and perform submitchange() then it will not update value. Below code works and being used in production

Database db = new Database();
Table t = db.Table.single(p=>p.Column == ColumnName )
t.ColumnNameValue = value;

db.submitchanges();

thats it.
NeoZ
14 May, 2010

#24 is the same as the example at the top. I don’t see how this can work. Typically you will display the data, receive changes, then save the changes. This doesn’t appear to allow for changes. When you include the changes it won’t work that way.
Rafat
7 Jun, 2010

I can only say one thing..that your post is the best:)
Nilesh
1 Feb, 2011

using (var context = new RegistrationByLinqEntities())
{
var valtemp = context.FornDatas.SingleOrDefault(a => a.id == id);
valtemp.Approve = app;
valtemp.CreateDate = DateTime.Now;
context.SaveChanges();
}
Alex
14 Feb, 2011

This is my way.

FL is a table, FLBackup is also a table, same record.
lets say I change FL some field values but I dont know what the field names are and I want to revert back to the original field values, even after a submitchanges has been done, as long as I keep FLBackup cached it can be done, which bascially keep the same PK and does an UPDATE

var cols = fl.GetType().GetProperties().Select(p =>
new
{
Prop = p,
Attr = (ColumnAttribute)p.GetCustomAttributes(typeof(ColumnAttribute), true)
.SingleOrDefault()
}).Where(p => p.Attr != null && !p.Attr.IsDbGenerated);

foreach (var col in cols)
{
col.Prop.SetValue(fl, col.Prop.GetValue(flbackup, null), null);
}
Result:
_gDBContext.GetChangeSet()
{Inserts: 0, Deletes: 0, Updates: 1}
Deletes: Count = 0
Inserts: Count = 0
Updates: Count = 1
Alex
14 Feb, 2011

I hope this helps someone, I spent a good 3 hours finding a way to update values without doing the manual
table.myfield = “newvalue”
submitchanges()
Bacx
14 Jul, 2011

Thx for the tutorial
Kevin
29 Sep, 2011

soo You need to check if the Sync Property of your table is not “Always” in order to the command works fine