查询结果不能枚举多次? [英] The query results cannot be enumerated more than once?

问题描述

我正在使用 LINQ to SQL 在 Sql server 2008 中获取 FullTextSearch 存储过程的搜索结果.我将该过程从服务器资源管理器拖到设计器，并使用适当的返回类型和参数创建了该方法.现在的问题是，我需要获取调用此方法的结果的计数，因此使用我的存储库方法(它将调用 Sproc 方法并将结果作为 IQueryable 返回)我进行了以下调用.

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();

这段代码在我每次尝试运行时都会生成一个 InvalidOperationException，异常显示(是的，你猜对了！)查询结果不能被枚举多次."

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."

为 Sproc 生成的方法返回 ISingleResult 应该没问题.据我所知.我需要在我的视图中支持分页，所以我需要知道总页数，这(再次AFAIK)只有在我可以获得所有项目的数量时才有可能.

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?

推荐答案

由于这是执行存储过程，因此您所有可爱的 Skip/Take 无论如何都是多余的...它别无选择，只能将所有数据带回来(存储过程调用是不可组合的).它唯一能做的就是不为其中的一些物化对象.

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

即使分页参数成为 SPROC 的一部分(以及在数据库中进行过滤，使用 ROW_NUMBER()/OVER(...)，或表变量、temp-表等) - 或者与 sproc 中的 OUTPUT 参数类似的东西:

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);

(我好像记得OUTPUT变成了ref，因为TSQLOUTPUT其实就是输入+输出)

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

这篇关于查询结果不能枚举多次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！