what is the difference between for and foreach in my case-CodePudding

I have two linked tables in the database. To display, I need to substitute compatible values in the collection. When I use a for loop the code works correctly, but when I use foreach I get an error that my server is already in use.

Correct case:(not working method in comments)

public IActionResult Index()
{
    IEnumerable<Product> objList = _db.Product;
    var arr = objList.ToArray();

    //foreach (var item in objList)
    //{
    //    item.Category = _db.Category.FirstOrDefault(category => category.Id == item.CategoryId);
    //};

    for (int i = 0; i < arr.Length; i  )
    {
        arr[i].Category = _db.Category.FirstOrDefault(category => category.Id == arr[i].CategoryId);
    }

    return View(arr);
}

CodePudding user response：

The for loop is iterating over the materialized _db.Product query, while the foreach is iterating over the unmaterialized query.

This is what has materialized the query with the for loop:

var arr = objList.ToArray();

While the foreach loop is using the unmaterialized query:

foreach (var item in objList)

The error is because _db.Product has yet to retrieve any data until either .ToArray() (or ToList) has been invoked, which retrieves all the results, whereas when just iterating over the unmaterialized query, each item is being queried in the foreach loop.

Change the foreach loop to iterate over the materialized result set by using the arr variable created with var arr = objList.ToArray();

Edit

As noted by Olivier's comment, all of this can be replaced by using EF's Relationship fixup. Something along the lines of the following should work:

Product[] products = _db.Product.Include(p => p.Category).ToArray();