How to Update Data with LINQ-to-SQL

demo

By Richard Bushnell

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 AttachedThe 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 later
product.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 UpdatesUnfortunately, 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 in product.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"
);


http://www.richardbushnell.net/2008/02/18/how-to-update-data-with-linq-to-sql/

demo

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!

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?

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

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

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;
        }

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).”

juseliao

记得从前刚学会骑自行车时，因为还不熟练，所以并不会转弯、刹车之类的技术！一天，在家闲着无聊，便骑着车出去溜达溜达！和父亲一起，当我们到达一个大坡时（下坡）我不会刹车，而父亲又把我护在里侧，只见这时，我前方出现了一个男人面对一棵大树正撅着屁股给自行车打气。我的自行车正对他的屁股~~~~于是，在我紧急的思考中，自行车已经快速的到达了他的屁股前，因为不会刹车，所以~~~男人被我的车篮狠狠的往前一撞，差点没撞到树上去



如何丰胸最有效，如何有效丰胸，怎么样才能丰胸，怎么样丰胸

juseliao

好帖 此贴不顶枉混论坛！！

一定要顶一下！！




最有效的丰胸产品,丰胸最有效的方法,健康丰胸,如何丰胸