iterating over DbSet<TEntity> vs IQueryable<out T>

Question

Using Entity Framework I select some data from a table and iterate over it with foreach loop. I am wondering when is the data getting queried in the following examples?

Example 1.

var countries = db.WorldCountries;
foreach(var item in countries)
{
    Console.WriteLine("Country: {0}", item.Country);
}

Example 2.

var countries = db.WorldCountries.Where(t => t.Country == "Country Name");
foreach(var item in countries)
{
    Console.WriteLine("Country: {0}", item.Country);
}

In the first example, countries is DbSet<WorldCountries> In the second example, countries is IQueryable<out WorldCountries>.

Without the .ToList() in the above examples how is the data retrieved? Is the entire data set retrieved when the foreach loop starts (as if .ToList() were called at the beginning of the first iteration) or queries are issued to the database on each iteration of the loop.

Thanks.

Answer 1

In both examples, the IQueryable<WorldCountries> is compiled to SQL and executed at the point you enter foreach (when foreach calls GetEnumerator). So you receive the result from the db just before the first iteration, not piece by piece on every single iteration.(The results come via a DataReader so actual data transfer may be done piece by piece for each iteration but what I mean is, there is not a separate SQL query on each iteration).

Note that DbSet<T> also implements IQueryable<WorldCountries>, so your both examples work the same, except the second one happens to include a where clause.

When you add .ToList, that iterates through and fills up a list before returning, so in that case you transfer all the necessary data from the db before moving on to the next statement.