Does it load the data from database?

Question

Assume we have a method like this:

public IEnumerable<T> FirstMethod()
{
    var entities = from t in context.Products
                   where {some conditions}
                   select t;

    foreach( var entity in entities )
    {
        entity.SomeProperty = {SomeValue};
        yield return entity;   
    }
}

where context is a DataContext that is generated by Linq to SQL designer.

Does "FirstMethod" load the data into memory from database (because of the foreach loop) or will it still defer-load it until another foreach loop that doesn't have "yield return" is found in another method like the following?

public void SecondMethod()
{
    foreach( var item in FirstMethod() )
    {
        {Do Something}
    }
}

Answer 1

The latter (deferred); FirstMethod is an iterator block (because of yield return); this means that you have a chain of iterators. Nothing is read until the final caller starts iterating the data; then each record is read in turn during the final caller's foreach (between which the connection/command is open).

The using that surrounds foreach (under the bonnet) ensures that the connection is closed if the foreach is abandoned half-way-through.

If you want to load the data earlier, use .ToList() or .ToArray() to buffer the data locally - but note that this breaks "composition" - i.e. the caller can no longer add extra Where etc clauses (which they can if it returns a raw IQueryable<T>).

---

Re your question:

public IEnumerable<T> FirstMethod()
{
    var entities = from t in context.Products
                   where {some conditions}
                   select t;

    foreach( var entity in entities.AsEnumerable() )
    {
        entity.SomeProperty = {SomeValue};
        yield return entity;   
    }
}

The AsEnumerable is the key here; it ends the composable IQueryable<T> chain, and uses LINQ-to-Objects for the rest.