LINQ: How to perform an update like UPDATE <T> SET Update(<T>, <T>) SELECT FROM clause in LINQ?

Question

Hi,

I want to update and insert some records based on a csv I read. The insert is not a problem, but how to update a bunch of data in a single statement? I have not clear how to join them.

Update Item
Set
  Name = t.Name
From
  Item i, TextFile t
Where
  i.ItemNo = t.ItemNo

For the Name = t.Name I created an private Item UpdateItem(Item originalItem, Item newItem) in which the logic for updating is present.

I want to know to to call this functions and perform an db.SubmitChanges() with the changed records.

Any help appreciated.

Answer 1

I'm making the assumption that TextFile is an in memory collection of objects that you've read from the CSV file and Items is a Table in the data context from your database. In that case I don't think you will be able to do an actual join without first fetching all of the items from the database into an in memory collection as well -- which may be a costly operation. The sample below selects just those items and its matching new name from the text file into a new collection, then iterates through that collection and sets the name on the item. It won't use your UpdateItem method.

var textFile = ...collection of objects from CSV...

var textIDs = textFile.Select( t => t.ItemNo );

using (var db = new DataContext())
{
  var toUpdate = db.Items
                   .Where( i => textIDs.Contains( i.ItemNo ) )
                   .ToList()  // <--- this will force the query
                   .Select( i => new
                    {
                        Item = i,
                        NewName = textFile.Where( t => t.ItemNo == i.ItemNo )
                                          .Single()
                                          .Name
                    });

  foreach (var item in toUpdate)
  {
      item.Item.Name = item.NewName;
  }

  db.SubmitChanges();
}