The query results cannot be enumerated more than once?

linq-to-sql invalidoperationexception
Galilyou picture Galilyou · Sep 1, 2009 · Viewed 31.9k times · Source

I'm using LINQ to SQL to get a search result of a FullTextSearch stored procedure in Sql server 2008. I dragged the procedure from the server explorer to the designer, and got the method created with the appropriate return type and parameters. Now the problem is, I need to get the Count of the result of calling this method, so using my repository method (which will call the Sproc method and return the result as IQueryable) I make the following call. 

var result = repository.FullTextSearch(searchText);
        int resultsCount = result.Count();
        var ret = result.Skip((pageNumber - 1) * PageSize).Take(PageSize).ToList();

This code generates an InvalidOperationException each time I try to run it, the exception says (yeah, you guessed it!) "The query results cannot be enumerated more than once."

The method that was generated for the Sproc returns ISingleResult which should be O.K. AFAIK. I need to support paging on my view, so I need to know the total number of pages, which (AFAIK again) is only possible if I could get the count of all items.

What am I missing here, guys?

Answer

Ronald Wildenberg picture Ronald Wildenberg · Sep 1, 2009

What you can do is add a ToList() call after repository.FullTextSearch(searchText). This way, the results are retrieved from the server, after which you can do with them whatever you want (since they are now loaded in-memory).

What you are trying to do now is run the same SQL query twice, which is rather inefficient.

Related questions

What is the syntax for an inner join in LINQ to SQL?

I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#. How do you represent the following in LINQ to SQL: select DealerContact.* from Dealer inner join …

Read More
Difference Between Select and SelectMany

I've been searching the difference between Select and SelectMany but I haven't been able to find a suitable answer. I need to learn the difference when using LINQ To SQL but all I've found are standard array examples. Can someone …

Read More
Convert Linq Query Result to Dictionary

I want to add some rows to a database using Linq to SQL, but I want to make a "custom check" before adding the rows to know if I must add, replace or ignore the incomming rows. I'd like to …

Read More