How to loop through 2 nested recordsets without open DataReader error?

Question

My setup: asp.net mvc web application with an attached sql database.

Given 3 sample tables such as:

Where I have to get a list of all invoices for a given contract and the total invoice sum (Sum of UnitsSold * UnitPrice) of their sold items.

I tried below controller action:

    public IEnumerable<Invoice> Get(int contractId)
    {
        IEnumerable<Invoice> invoices = db.Invoices.Where(i => i.ContractId == invoiceId);

        foreach (var invoice in invoices){
            var items = db.Items.Where(t => t.InvoiceId == invoice.InvoiceId);
            foreach (var item in items){
                   invoice.Total += item.UnitsSold * item.UnitPrice;
            }

        }
        return invoices;
    }

But at var items = db.Items.Where(t => t.InvoiceId == invoice.InvoiceId);

I get: Invalid Operation Exception: This command is already assigned to an open DataReader, which must first be closed. I also tried IQueryable instead of IEnumerable, but still faulty.

Any help for getting this right would be very appreciated.

Edit and solution:

In fact the above sample is a simplified version of my problem but the given posts helped me to come up with a solution like:

    public IEnumerable<ContractInvoiceViewModel> Get(int contractId)
    {
        // here the total product sum for all invoices is built and stored in the respective invoice field
        var invoices = db.Invoices.Where(i => i.ContractId == contractId).ToList();
        foreach (var invoice in invoices)
        {
            var existingInvoice = db.Invoices.Find(invoice.InvoiceId);
            var items = db.Items.Where(t => t.InvoiceId == invoice.InvoiceId).ToList();
            decimal? tempSum = 0.00m;
            foreach (var item in items)
            {
                tempSum += item.UnitPrice * item.UnitsSold;
            }
            existingInvoice.Total = tempSum;
            db.Entry(existingInvoice).State = EntityState.Modified;
            db.SaveChanges();
        }

        //  here the viewmodels for the view are collected
        IEnumerable<Invoice> invoicesForView = db.Invoices.Where(i => i.ContractId == contractId);
        var contract = db.Contracts.Find(contractId);
        var customer = db.Customers.Find(contract.CustomerId);

        IList<ContractInvoiceViewModel> result = new List<ContractInvoiceViewModel>();
        foreach (var invoiceItem in invoicesForView)
        {
            var model = new ContractInvoiceViewModel
            {
                InvoiceId = invoiceItem.InvoiceId,
                ContractId = invoiceItem.ContractId,
                ContractDate = contract.ContractDate,
                InvoiceDate = invoiceItem.InvoiceDate,
                Customer = customer.Name,
                Info = contract.Info,
                Total = invoiceItem.Total,
            };
            result.Add(model);
        }
        return result;
    }

I chose the ToList() approach because it worked. The approach with the navigation property works too but there I also had use ToList() in order to be able to submit the database changes for the Invoice.Total field inside the outer loop.

Answer 1

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.tolist?view=netframework-4.8#System_Linq_Enumerable_ToList__1_System_Collections_Generic_IEnumerable___0__

From the documentation itself;

The ToList(IEnumerable) method forces immediate query evaluation and returns a List that contains the query results. You can append this method to your query in order to obtain a cached copy of the query results.

Operations on an IEnumerable that is connected to the datasource must be first transferred to memory/cache prior to editing properties.

public List<Invoice> Get(int contractId)
{
   List<Invoice> invoices = db.Invoices.Where(i => i.ContractId == invoiceId).ToList();

   foreach (var invoice in invoices){
      var items = db.Items.Where(t => t.InvoiceId == invoice.InvoiceId).ToList();
      foreach (var item in items){
         invoice.Total += item.UnitsBought * item.UnitPrice;
      }
   }
   return invoices;
}

Answer 2

This is happening because items.GetEnumerator() needs to run a SQL query and start fetching results, but invoices is in the middle of reading a result from the database. And you (normally) can't run two different queries at the same time on the same database connection. The first query results need to be read to the end before a new query can be run. The modifications are only happening in-memory at this point, and would be saved to the database later with a call to db.SaveChanges(). I realize that this answer is from @DavidBrowne-Microsoft 's comments and partially from the answer by @JerdineSabio . I just felt that the question needed the answer and all pertinent information to be consolidated.

Option 1: Use ToList() to load the query into memory

public List<Invoice> Get(int contractId)
{
   List<Invoice> invoices = db.Invoices.Where(i => i.ContractId == invoiceId).ToList();

   foreach (var invoice in invoices){
      var items = db.Items.Where(t => t.InvoiceId == invoice.InvoiceId).ToList();
      foreach (var item in items){
         invoice.Total += item.UnitsBought * item.UnitPrice;
      }
   }
   return invoices;
}

Option 2: (Recommended) Include the items needed in initial query. (assuming you have a navigation property on invoice)

public IEnumerable<Invoice> Get(int contractId)
{
    IEnumerable<Invoice> invoices = db.Invoices.Include(i => i.Items).Where(i => i.ContractId == invoiceId);

    foreach (var invoice in invoices){
        var items = invoice.Items;
        foreach (var item in items){
               invoice.Total += item.UnitsBought * item.UnitPrice;
        }

    }
    return invoices;
}