The result of a query cannot be enumerated more than once

Question

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.

Canna

You're missing a .ToList(), I just can't see where... I'll grab a coffee and take another look – Levi

Answer 1

Try explicitly enumerating the results by calling ToList().

Change

foreach (var item in query)

to

foreach (var item in query.ToList())

Keening

@Canna 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. – Ambrosius

@Ambrosius - 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? – Keening

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. – Tertial

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. – Dorr

Answer 2

Try replacing this

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

with

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

and everything will work well.

Busby

tolist() make entity free , if you not use tolist it expected that entity not released and you will work on it – Busby

Answer 3

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=>...);
}

Neuro

That's exactly what the accepted answer said. Please don't repeat answers. – Burleigh

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 – Neuro

OK, but then it duplicates this answer. – Burleigh

Answer 4

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

Unbolt