Best way to update in Linq To SQL

Question

Hello All, I have several entity classes that I use for parsing fixed width text files as well as utilizing Linq to SQL. I use these classes to parse data from said text files, and compare to data in the database.

One of these entities has a lot of properties, and I don't want to waste time setting each individual property on the Linq result object.

Is there a way to tell Linq "Here's my object, use this to update the record"? Here's code I'm working on:

if (partialContent.MonthlyAddChange == "A")
   {
       bookContentTable.InsertOnSubmit(partialContent);
   }
   else if (partialContent.MonthlyAddChange == "C")
   {
       var query = from bookContent in bookContentTable
                   where bookContent.EAN == partialContent.EAN
                   select bookContent;

       if (query != null)
       {
           // Do something with query.First()
       }
   }
}

Is it better to delete the record and do an InsertOnSubmit() in this case?

Answer 1

I would not delete/recreate. Assuming that the two object have the same interface with respect to the properties that you want to update, I would use reflection and copy the values of the change to the existing object. If any of the values are different from the original, the record will get marked as needing to be updated and SubmitChanges will take care of it.

For example (with little error checking):

 foreach (var bookInfo in bookContent.GetType().GetProperties())
 {
     var partialInfo = partialContent.GetType().GetProperty( bookInfo.Name );
     if (partialInfo != null)
     {
         bookInfo.SetValue( partialInfo.GetValue( partialContent, null ) );
     }
 }

If you knew that they were the same type you could reuse the first PropertyInfo instead of getting a new one for partialContent.

Answer 2

I think the concept of editing a record is different from deleting and inserting a new one. Basically, I think an ORM should abstract away primary key generation and other related stuff. By deleting and inserting, you might be removing the integrity of the record (probably issuing a new primary key, making referenced entities invalid and so forth...). I suggest updating the record whenever the action you are taking is conceptually an update.

Answer 3

I'm thinking that you could use something like DataContext.Table.Attach(record, true) and then DataContext.SubmitChanges(). But I don't have it totally fleshed out...

So now I've done a test. This will only work if you don't require concurrency checking (i.e. you're the only one updating the table).

Here's my table

People
PersonID int
FirstName varchar(50)
LastName varchar(50)

I populated the table with the following record

> PersonID     FirstName    LastName
> 1            Jason        Punyon

I created a LINQ2SQL DataContext with just this table called PeopleDataContext and on every property of the People Class I set the UpdateCheck property of each record property to Never.

Here's the code:

static void Main(string[] args)
{
    var p = new People();
    p.PersonID = 1;
    p.FirstName = "Jason";
    p.LastName = "This is a new last name";


    using (var db = new PeopleDataContext())
    {
        db.Peoples.Attach(p, true);
        db.SubmitChanges();
    }
}

And it works successfully. No reflection or anything, but like I said, you lose concurrency checking.

Answer 4

You can consider using automapper to copy the values for you. Check this for more info: http://www.lostechies.com/blogs/jimmy_bogard/archive/2009/01/22/automapper-the-object-object-mapper.aspx

Answer 5

Is it better to delete the record and do an InsertOnSubmit() in this case?

No, definitely not - just consider referential integrity that any good, stable DB design should use. If your record is already being used by other rows, you cannot simply remove it and re-insert it - you would break those integrity constraints.

If you're just changing a few values, update the existing row - much easier and much more consistent.

Marc