Retrieve Full Live Collection With LinqToSql The Update Each Record

Question

Hi,

I want to retrieve a collection of Orders and update the OrderStatus on each order within that  LinqToSqlContext collection.

However, as I understand it, if I perform a foreach on the query, a separate database call will be made to retrieve each order.

And if I call .ToList(), I lose the LinqToSql context relation with those objects.

How can I get the entire collection in one call, yet keep the collection members connected to the context?

Thanks,

Mark

Answer 1

What do you mean "I lose the L2S context relation"?

LS

---

Lloyd Sheen

Answer 2

Hi Mark;

To your statement, "However, as I understand it, if I perform a foreach on the query, a separate database call will be made to retrieve each order.", This all depends on how you are querying the database. If you make a call to retrieve a single record and modify it then get the next single record and do the same until you have modified the records needed then yes. If you form your query to return all the records you wish to modify in one query then no. See sample code and comments.

To your statement, "And if I call .ToList(), I lose the LinqToSql context relation with those objects.", Linq to SQL by default has object tracking set on so if you have NOT turned off object tracking the objects in the List will reference the objects in the data context.

To your statement, "How can I get the entire collection in one call, yet keep the collection members connected to the context?", see my code snippet for a same with comments.

// Using the Microsoft AdventureWorks sample data base
DataClasses1DataContext ctx = new DataClasses1DataContext();

// This query make one hit to the database to get the result of how many records were requested
var query = (from o in ctx.SalesOrderHeaders
             where o.Status == 5
             select o).Take(50).ToList();

// The change in status to the requested record are made to the records in the data context
// because all records in the list are references to objects in the data context.
foreach (var salesOrderHeader in query)
{
    salesOrderHeader.Status = 1;
}

// This causes the data context to issue 1 update for each of the records that have been modified
// in this case 50 updates were issued.
ctx.SubmitChanges();

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".