The result of a query cannot be enumerated more than once

63

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.

Halcyon

출처 c# asp.net linq entity-framework entity-framework-4
  • You're missing a .ToList(), I just can't see where... I'll grab a coffee and take another look - Smudge202

3 답변

126

Try explicitly enumerating the results by calling ToList().

Change

foreach (var item in query)

to

foreach (var item in query.ToList())

  • That worked! Can you explain why it works? - Halcyon
  • @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
  • @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
  • 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
  • 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
6

Try replacing this

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

with 

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

and everything will work well.

  • This worked for me but I have no idea why. - Nicholas Pickering
  • tolist() make entity free , if you not use tolist it expected that entity not released and you will work on it - hosam hemaily
-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

Linked

Related

Latest