72

I am using the entity framework (ef) and am getting the following error:

"The result of a query cannot be enumerated more than once.".

I have a repository class which contains the ef data context. I then have a controller class (not to be confused with MVC controllers) which contains an instance of the repository. So far so good... I have a search method on the controller which is supposed to return an array of RadComboBoxItemData, which is used to populate a Telerik RadComboBox control.

public RadComboBoxItemData[] Search(int id, string searchText)
{
    var query = context.Search(id, searchText);
    List<RadComboBoxItemData> result = new List<RadComboBoxItemData>();
    foreach (var item in query)
    {
        RadComboBoxItemData itemData = new RadComboBoxItemData();
        itemData.Text = ""; // assign some text here..;
        itemData.Value = ""; /*assign some value here..*/
        result.Add(itemData);
    }

    return result.ToArray();
}

When I debug my code, I can get into the foreach loop, but then I get an error saying:

An exception of type 'System.InvalidOperationException' occurred in System.Data.Entity.dll but was not handled in user code

Additional information: The result of a query cannot be enumerated more than once.

My entity uses a function import of an existing stored proc.

// EF repository method calling the function imported method on the data context.
public IEnumerable<SearchItem> Search(int id, string searchText)
{
    return this.entityContext.Search(id, searchText);
}

The function import Search calls a stored precedure to return a collection of SearchItem.

I have a feeling that the foreach loop can't iterate because of something with the ef.

CatDadCode
  • 58,507
  • 61
  • 212
  • 318
Halcyon
  • 14,631
  • 17
  • 68
  • 99

4 Answers4

153

Try explicitly enumerating the results by calling ToList().

Change

foreach (var item in query)

to

foreach (var item in query.ToList())
Yakimych
  • 17,612
  • 7
  • 52
  • 69
  • 5
    @Halcyon it works because using `ToList` you are retrieving ALL results from the database in this list, from now on linq methods aren't going to work on the database. This can be a serious performance issue depending on the amount of data you have and what you will do with it. – BrunoLM Jul 21 '11 at 20:24
  • 4
    @BrunoLM - In this case the OP is already iterating through ALL the query results. In case more "linq methods" need to be executed against the database he can continue using the `query` variable. Not only don't I see a problem with the solution, but also it is the best possible way to solve the OP's problem. Care to explain the downvote? – Yakimych Jul 22 '11 at 08:01
  • 15
    The reason `.ToList()` works is that when you use an *IEnumerable*, the collection is iterated in a **lazy** way. In this case, each time the *foreach* loop gets an item, the query retrieves the item from your db or whatever you are using, if you want to iterate again it wont be able to redo the query. When you use `.ToList()`, all the items in the *IEnumerable* are processed and saved as a list. Then you can use the created list as many times as you want. – Vicro Aug 27 '14 at 02:30
  • 9
    to further clarify. The root of the problem is that store procedures return forward only result sets. This means once you reached the end then you must redo the query to get back to the start. A to list however converts the result set into a List data structure which can be enumerated as many times as you like. – Ageis Feb 24 '15 at 13:21
11

Try replacing this

var query = context.Search(id, searchText);

with 

var query = context.Search(id, searchText).tolist();

and everything will work well.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
hosam hemaily
  • 412
  • 5
  • 17
-1

Problematic code calling an stored procedure:

var resultSP = db.StoredProcedure(id);

if (resultSP != null)
{
    var count = resultSP.Count();
    
    var list = resultSP.Select(x=>...);
}

Fixed, store in a variable with ToList() and reuse it:

var resultSP = db.StoredProcedure(id);

if (resultSP != null)
{
    var resultSP_List = resultSP.ToList();
    
    var count = resultSP_List.Count();
    
    var list = resultSP_List.Select(x=>...);
}
Dani
  • 1,825
  • 2
  • 15
  • 29
  • That's exactly what the [accepted answer](https://stackoverflow.com/a/5723643/861716) said. Please don't repeat answers. – Gert Arnold Apr 01 '22 at 12:18
  • Is not exactly that acepted answer said, if you use 2 tolist in same query, error persist @GertArnold, the answer does not say that you have to save the to list and reuse the variable – Dani Apr 01 '22 at 20:25
  • OK, but then it duplicates [this answer](https://stackoverflow.com/a/46904364/861716). – Gert Arnold Apr 01 '22 at 21:08
-5

if you getting this type of error so I suggest you used to stored proc data as usual list then binding the other controls because I also get this error so I solved it like this ex:-

repeater.DataSource = data.SPBinsReport().Tolist();
repeater.DataBind();

try like this

Mong Zhu
  • 23,309
  • 10
  • 44
  • 76
rishi kaithwas
  • 1