How to update with Linq-To-SQL?

Question

I need to update values but I am looping all the tables values to do it:

public static void Update(IEnumerable<Sample> samples
    , DataClassesDataContext db)
{
    foreach (var sample in db.Samples)
    {
        var matches = samples.Where(a => a.Id == sample.Id);
        if(matches.Any())
        {
            var match = matches.First();
            match.SomeColumn = sample.SomeColumn;
        }
    }
    db.SubmitChanges();
}

I am certain the code above isn't the right way to do it, but I couldn't think of any other way yet. Can you show a better way?

Answer 1

Yes, there is a simpler way. Much simpler. 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. Your code would look something like this.

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

In this case, Linq to SQL is using the keys to match and update records so as long as your keys are in synch, you're fine.

Answer 2

With Linq2Sql (or Linq to Entities), there is no way* to update records on the server without retrieving them in full first, so what you're doing is actually correct.

If you want to avoid this, write a stored procedure that does what you want and add it to your model.

I'm not entirely sure if that was your intended question however :)

*: There are some hacks around that use LINQ to build a SELECT statement and butcher the resulting SELECT statement into an UPDATE somehow, but I wouldn't recommend it.