CODEWITHSUNDEEP
asp.netsqllinq
Full Time Skeleton
Full Time Skeleton

Reputation: 1740

LINQ to SQL - updating records

Using asp.net 4 though C#.

In my data access layer I have methods for saving and updating records. Saving is easy enough but the updating is tedious.

I previously used SubSonic which was great as it had active record and knew that if I loaded a record, changed a few entries and then saved it again, it recognised it as an update and didn't try to save a new entry in the DB.

I don't know how to do the same thing in LINQ. As a result my workflow is like this:

  1. Web page grabs 'Record A' from the DB
  2. Some values in it are changed by the user.
  3. 'Record A' is passed back to the data access layer
  4. I now need to load Record A again, calling it 'SavedRecord A', update all values in this object with the values from the passed 'Record A' and then update/ save 'SavedRecord A'!

If I just save 'Record A' I end up with a new entry in the DB.

Obviously it would be nicer to just pass Record A and do something like:

RecordA.Update();

I'm presuming there's something I'm missing here but I can't find a straightforward answer on-line.

Upvotes: 7

Views: 9570

Answers (3)

Anders Abel
Anders Abel

Reputation: 69250

When LINQ-to-SQL updates a record in the database, it needs to know exactly what fields were changed in order to only update those. You basically have three options:

  • When the updated data is posted back to the web server, load the existing data from the database, assign all properties to the loaded object and call SubmitChanges(). Any properties that are assigned the existing value will not be updated.
  • Keep track of the unmodified state of the object and use Attach with both the unmodified and modified values.
  • Initialize a new object with all state required by the optimistic concurrency check (if enabled, which it is by default). Then attach the object and finally update any changed properties after the attach to make the DataContext change tracker be aware of those updated.

I usually use the first option as it is easiest. There is a performance penalty with two DB calls but unless you're doing lots of updates it won't matter.

Upvotes: 1

patel.milanb
patel.milanb

Reputation: 5982

let's say you have a product class OR DB, then you will have to do this.

    DbContext _db = new DbContext();

    var _product = ( from p in _db.Products
                                    where p.Id == 1  // suppose you getting the first product
                                    select p).First();  // this will fetch the first record.

     _product.ProductName = "New Product";

     _db.SaveChanges();

      // this is for EF LINQ to Objects
     _db.Entry(_product).State = EntityState.Modified;  
     _db.SaveChanges();

-------------------------------------------------------------------------------------
this is another example using Attach
-------------------------------------------------------------------------------------

public static void Update(IEnumerable<Sample> samples , DataClassesDataContext db)
{
    db.Samples.AttachAll(samples);
    db.Refresh(RefreshMode.KeepCurrentValues, samples)
    db.SubmitChanges();
}

If you attach your entities to the context and then Refresh (with KeepCurrentValues selected), Linq to SQL will get those entities from the server, compare them, and mark updated those that are different

Upvotes: 2

smartcaveman
smartcaveman

Reputation: 42246

You can accomplish what you want using the Attach method on the Table instance, and committing via the SubmitChanges() method on the DataContext.

This process may not be as straight-forward as we would like, but you can read David DeWinter's LINQ to SQL: Updating Entities for a more in depth explanation/tutorial.

Upvotes: 2

Related Questions