Database is locked when inside a foreach with linq without ToList()

Question

I've tried looking for an answer but couldn't find it. The 'issue' is simple:

If I have a collection of items using linq as follows:

var items = db.AnyTable.Where(x => x.Condition == condition).ToList();

And another collection of items using linq but without a ToList():

var items2 = db.AnyTable.Where(x => x.Condition == condition);

If now I try to iterate each item with a foreach (I didn't try while or other kind of iteration methods):

foreach (var item in items) 
{
    int i = 2;// Doesn't matter, The important part is to put a breakpoint here.
}

If I stop the code at the breakpoint and I try to update the AnyTable on SQL Management Studio everything is OK. BUT IF!:

foreach (var item in items2) 
{
    int i = 2;// Doesn't matter, The important part is to put a breakpoint here.
}

If now I try to update (while on the breakpoint) the AnyTable on SQL Management Studio I won't be able to do it (TimeOut).

Why is the ToList() making such a difference?

From what I've learnt a particular difference is WHEN the query is being executed (on items it is executed at the declaration and on items2 it is executed on the foreach statement). Why is that preventing me to update the AnyTable ?

Answer 1

The linq query is only translated to SQL query when you access the data. When you call ToList() you create a SQL query that returns all the data to the code from the db. Now the items var is entirely in memory and no more work is done against the db. In the second example, you are using foreach, which opens a trnasaction in the db, and while this transaction is still open, you are trying to change the data you will get the timeout, since the table is held by the open LINQ query against the DB.