How can I solve "The query results cannot be enumerated more than once"?

Question

if i try to write below codes : error return to me: The query results cannot be enumerated more than once

  public void StartTransaction()
        {
            using (var stockMovementCtx = new StockMovementCtxDataContext())
            using (var scope = new TransactionScope())
            {

                var stockMovementItems = from s in stockMovementCtx.spStockMovementForTransaction(TicketID, ItemTypeNo, ItemID, TransactionType,
                                                                                                  FromLocation, ToLocation, Qty, PersonelNo, cuser
                                                                                                  )
                                         select s;

                ReturnCode = stockMovementItems.First().ReturnCode;
               // MessageBox.Show(stockMovementItems.First().ToString());
                ReturnMsg = stockMovementItems.First().ReturnMessage;
                TransactionType = stockMovementItems.First().TransactionType;
                TicketID = stockMovementItems.First().TicketID;

Details:

Detail :

Answer 1

Short fix:

var FirstChild = stockMovementItems.First();

then

ReturnCode = FirstChild.ReturnCode;
//etc...

or

(from s in stockMovementCtx.spStockMovementForTransaction(TicketID, ItemTypeNo, ItemID, TransactionType, FromLocation, ToLocation, Qty, PersonelNo, cuser) select s).ToList();

but the latter is kind of an abuse of Linq.

Longer answer is that whatever stockMovementCtx.spStockMovementForTransaction(..) is, it only supports forward reading. Thus when you call First() you advance to the next result and cannot go back.

Answer 2

I am not familiar with this error.
But anyway - why should you enumerate more than once?
It will be more efficient with just one enumeration.

public void StartTransaction()
        {
            using (var stockMovementCtx = new StockMovementCtxDataContext())
            using (var scope = new TransactionScope())
            {

                var stockMovementItems = from s in stockMovementCtx.spStockMovementForTransaction(TicketID, ItemTypeNo, ItemID, TransactionType,
                                                                                                  FromLocation, ToLocation, Qty, PersonelNo, cuser
                                                                                                  )
                                         select s;

                var item = stockMovementItems.FirstOrDefault()
                if (item != null)
                {
                    ReturnCode = (item.ReturnCode;
                   // MessageBox.Show((item.ToString());
                    ReturnMsg = item.ReturnMessage;
                    TransactionType = item.TransactionType;
                    TicketID = item.TicketID;

Answer 3

You should not use the First() the whole time! Try catching the value that comes out of the First() into a variable and use that variable to get the values ReturnCode and ReturnMessage from (or look if there is something like .current in the object).

The problem is that by calling First() you tell the cursor to move position, after calling the First() the second time the system thinks you are going backwards through the items which may not be allowed for this object (there are collections that do not allow you to walk back).

Best option is to capture the object returned from First() into a variable and use that for your work (this is also beter because you won't be reading from the collection each time).

Answer 4

You can do

var firstItem = stockMovementItems.First();
ReturnCode = firstItem.ReturnCode;
ReturnMsg = firstItem.ReturnMessage;
...

Answer 5

The reason is that underlying DataReader is using a forward only cursor. When you have got the first entry it cannot go back (as it would with another enumeration).

Answer 6

as I see

ReturnCode = stockMovementItems.First().ReturnCode; // MessageBox.Show(stockMovementItems.First().ToString()); ReturnMsg = stockMovementItems.First().ReturnMessage; TransactionType = stockMovementItems.First().TransactionType; TicketID = stockMovementItems.First().TicketID;

you enumerate more than once.. and why u are using First(); ? use ToList() at the end of your linq query instead of First(). And your problem will be solved. Istedigini sonuc tek bir kayittan ibaret degilse, her zaman icin ToList() kullanmanda fayda vardir.

Also you can set the first value of your query result to varible, then call your results on that variable. Boylece 1 kere enumerate etmis olur, diger sonuclarini atadigin degiskenden alabilirsiniz.

var myResult = stockMovementItems.First();
ReturnCode = myResult.ReturnCode;
ReturnMsg = myResult.ReturnMessage;

gibi..