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?

Strohben

Apparently the suggested refactoring by Marc Gravell is the best, goona do this once I have time to it. I take Marc's answer! Thanks again Guys! – Strohben

Answer 1

Since this is executing a stored procedure, all your lovely Skip / Take is largely redundant anyway... it has no choice but to bring all the data back (stored procedure calls are non-composable). The only thing it can do is not materialize objects for some of them.

I wonder if the better approach would be to refactor the code to make two calls:

int result = repository.FullTextSearchCount(searchText);
var result = repository.FullTextSearch(searchText, skip, take); // or similar

i.e. make the paging parameters part of the SPROC (and to the filtering at the database, using ROW_NUMBER() / OVER(...), or table-variables, temp-tables, etc) - or alternatively something similar with an OUTPUT parameter in the sproc:

int? count = null;
var result = repository.FullTextSearch(searchText, skip, take, ref count);

(I seem to recall that OUTPUT becomes ref, since TSQL OUTPUT is really input+output)

Biddick

Totally agree on the refactoring part here, but, time is not permitting right now. For now I think I will add another method to the repository "int FullTextSearchCount()". This seems to work as I speak. The refactoring tip is on the To-Do now Marc. +1 thanks. – Strohben

Answer 2

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.

Lukasz

I would argue that bring the entire FTS result back and materializing everything is substantially more inefficient than running two (but constrained) queries. – Biddick

I think so too Marc. I mean if I added .ToList() as you suggested rwwilden then I will get the entire search result, which can be huge. I don't want to get them all now, I just want 10 of them, that's why I'm .Skip()ping. – Strohben

I agree that bringing the entire result set back to the server is more inefficient than performing two queries. However, since it is a stored procedure call, that will happen anyway (as Marc correctly noted in his answer). – Lukasz

Yes, you're right rwwilden, I totally forgot about how the sproc result set will be brought eagerly. I stand corrected. Thanks +1 – Strohben

Doing the count and the page in a single round-trip would definitely be the best solution. Requires some rewriting of your sproc of course. – Lukasz

@Marc Gravell I know this a long time ago, but could you elaborate on the Count + Page in the same trip? – Ideate

@Marc What MarcG means is that you write your sproc so that it returns two results: the page of data you wish to retrieve and the total count of objects. This way you call the database just once. – Lukasz

@Ronald Wildenberg That is possible? To have a sproc that I can not only retrieve the count of the rows, but also the rows as well? (Oh and ATMark, not ATMarc so I get a notification if you answer :) ) – Ideate

@Ideate That is definitely possible. You can find an example here: msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx – Lukasz

Answer 3

Using ToList() can help to avoid this problem.

var result = repository.FullTextSearch(searchText).ToList();

Royceroyd

Answer 4

I would suggest that if you need the count, execute the result first. and then run the count from the list itself, as you don't use resultsCount in your result execution.

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

Helicoid

The idea is that resultsCount represents the total number of results. – Lukasz

Yeah, Kamal! I need to get the Count of all the results. Not only the partial part I'm going to Take. – Strohben