Using Linq, I get this: There is already an open DataReader associated with this Command which must be closed first

Question

Here's what I'm trying to do:

IEnumerable<OfficeView> allOffices = GetAllOffices(); //this method also uses some linq query
foreach (var office in allOffices)
{
    officeSales.Add(
             new Tuple<int, decimal>(office.Id, GetSaleAmount(office.Id, someParams)));
}


public decimal GetAgentSaleAmount(int officeRef, someTypes someParams)
{
    var q = ObjectQuery.Where
                (i => i.officeId == officeRef && i.someOtherStuff == someParams)
                .ToList();

    return q.Sum(i => i.NetPrice);
}

I can't set MultipleActiveResultSets = true. Instead, as suggested here, I tried to do .ToList() before doing the summation, but got the same error. (and even if I wouldn't get the error, I think it would cause heavy load to fetch everything just to get the summation of one field)

I will be super thankful for any suggestion to solve this problem!

Update: The problem was all about GetAllOffices(), which returned IEnumerable (which leaves the DataReader open). I changed the first line as below and it worked. Thanks a lot guys :)

IEnumerable<OfficeView> allOffices = GetAllOffices().ToList();

why can't you set Multiple Active Results? – Akash Kava Jul 29 '13 at 06:29 — Akash Kava, Jul 29 '13 at 06:29

score 2 · Accepted Answer · answered Jul 29 '13 at 05:35

2

Error said that reader is already opened, that means that you have run some query before which needs to close. Write .ToList() to that linq query and do same for other queries too.

answered Jul 29 '13 at 05:35

nikodz

727
5
13

And what about other queries? – nikodz Jul 29 '13 at 06:03
no other query is being ran :( apparently, the problem is not with `sum`, I removed sum, and still get the error.. going to explore it more, thanks – Yalda Jul 29 '13 at 06:15
Do you have just one `Where` or you have other operations or more then one `Where`? – nikodz Jul 29 '13 at 06:44
just one where, but lots of conditions in it – Yalda Jul 29 '13 at 07:22
Try to do `.ToList()` after first condition – nikodz Jul 29 '13 at 07:53

score 1 · Answer 2 · answered Jul 29 '13 at 06:50

1

I think you are trying to do both adding and querying at the same time.You need to finish adding "Sale" object to "Sales" collection before trying to calculate the sales value.

In other words, you cannot query until the transaction is committed. Hope that helps.

answered Jul 29 '13 at 06:50

Kosala W

2,133
1
15
20

Tried separating them, still no success. But the problem is not with the query in `GetSaleAgentAmount` either, since I'm using it somewhere else and works fine. I have `IEnimerable allOffices = GetAllOffices()`, which uses linq, and works fine in other parts of the projects, but I am suspecting something may be wrong with iterating on `allOffices` and using `linq` inside it. Thanks for the help anyway :) – Yalda Jul 29 '13 at 07:35
Please post your complete code. where have you initialized your entity model? You need to use "using (var dbcontxt = new MyEntities()){} if you have not used it. – Kosala W Jul 29 '13 at 08:43
Problem was `GetAllOffices()`. Did `GetAllOffices().ToList()` and it solved the problem. Thanks. – Yalda Jul 29 '13 at 10:10

Using Linq, I get this: There is already an open DataReader associated with this Command which must be closed first

2 Answers2