Does the for loop invocation result in fetching all records from the database and is there a way to-CodePudding

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.

CodePudding user response：

As stated here, using foreach directly on an EF IQueyable streams the data, loading each entity into memory individually.

This reduces memory consumption, with the caveat that the underlying table(s) will be locked for the duration of the loop execution.

Note that as highlighted by David in the comments, you should also disable tracking to make entities eligible for garbage collection after each iteration.

Thais can be done for an individual query using AsNoTracking:

foreach (var productInfo in query.AsNoTracking())

Or globally using QueryTrackingBehavior.