linq to sql - The query results cannot be enumerated more than once?

Question

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 1

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.