Does the for loop invocation result in fetching all records from the database and is there a way to control this behaviour?

Question

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Products
        .Select(product => new
        {
            ProductId = product.ProductID,
            ProductName = product.Name
        });

    Console.WriteLine("Product Info:");
    foreach (var productInfo in query)
    {
        Console.WriteLine("Product Id: {0} Product name: {1} ",
            productInfo.ProductId, productInfo.ProductName);
    }
}

I understand that the query gets executed on the database server when the for loop starts executing. Assuming the database server returns the entire result back to the .net application and the entire data is held in query variable.

Is there any way to limit this so as to prevent memory overflow issues.