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.
As stated here, using foreach directly on an EF IQueryable 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.