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

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.

-------------Problems Reply------------

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();
}

Category:linq Views:1 Time:2009-04-10
Tags: linq

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.223 (s). 11 q(s)