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.
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 )
foreach (var item in toUpdate)
item.Item.Name = item.NewName;