'The Query Results cannot be enumerated more than once'

Question

I have a sql server procedure I am accessing with Linq to Sql. When I execute the query I am getting the error 'The Query Results cannot be enumerated more than once'. The parameter is entered in the txtName text box and the results are displayed in the lstName listview.

 public void GetSearchString()
 {
     Data.Database.FRCDatabaseDatacontext context = 
         new Data.Database.FRCDatabaseDatacontext();
     var result = context.GetSearchProcedure(txtName.Text);
     foreach (GetSearchProcedureResult search in result)
         if ( search.UserGuid == 
               Workspace.Instance.ActiveUser.CurrentUserActiveDirectoryGuid)
         {
             lstName.ItemsSource = result.ToList();
         }
 }

This method will return every result, but I want to return the results where the guids match.

Thanks!

Data.Database.FRCDatabaseDatacontext context = 
    new Data.Database.FRCDatabaseDatacontext();
var result = context.GetSearchProcedure(txtName.Text);
lstName.ItemsSource = result.ToList();

Solution 1

You are trying to enumerate the data more than once - once in the foreach and then again with the .ToList() for each matching UserGuid, which is causing the error.

Perhaps this LINQ select statement will help:

public void GetSearchString()
{
    Data.Database.FRCDatabaseDatacontext context = new Data.Database.FRCDatabaseDatacontext();

    lstName.ItemsSource = (from s in context.GetSearchProcedure(txtName.Text) 
                  where s.UserGuid == Workspace.Instance.ActiveUser.CurrentUserActiveDirectoryGuid
                  select s).ToList();
}

Solution 2

public void GetSearchString()
  {
      var context = new Data.Database.FRCDatabaseDatacontext();
      var result = context.GetSearchProcedure(txtName.Text);
      var itemSource = result.ToList();

        foreach (GetSearchProcedureResult search in itemSource)
        if (search.UserGuid == Workspace.Instance.ActiveUser.CurrentUserActiveDirectoryGuid)
        {
            lstName.ItemsSource = itemSource;
        }
    }

Anyway, It will be better to pass the parameter to procedure instead recalculating it in the code

Solution 3

Calling .ToList on an IQueryable causes it to be materialized (i.e. the query is passed on to the back-end provider). Obviously, this is something that you'd probably prefer to only do once, as it's likely to be an expensive operation. This is what the error is trying to tell you.

If you only call .ToList once and store the result, the problem should go away.

Comments

[spender]

I tidied up your code because you presented it like the dog's dinner. Perhaps you might be missing a brace, but I did not want to assume. It's not clear how the last block of code relates to your question.

[Servy]

+1 for showing how to adjust the query so that it returns the proper result, instead of just adding the ToList to the OP's query and filtering it in memory. Although note he's not just executing the query twice, he's executing it once, plus an additional time for each matching UserGuid, which could be quite a few times.