Linq：大型数据集的返回结果计数
我有 IEnumerable，它是数据库查询的结果，它有一个大数据集，多达 10000 条记录。我需要在网页上显示计数以进行分页。我该怎么做，使用 .Count() 将导致异常，例如 1The底层提供程序在 Open` 上失败或花费太长时间。
有没有办法可以查询数据库以通过 linq-sql 获取结果计数？
I have IEnumerable that is result of database query that has a large dataset, as much as 10000 records. I need the count to display on the webpage for pagination. How can I do it, using .Count() will result in exceptions like 1The underlying provider failed on Open` or takes way too long.
Is there a way I can query database to get the count for results by linq-sql?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可能是您使用 LINQ 的方式。如果您像这样调用查询：
这实际上只会调用查询以从数据库返回计数。如果你做了这样的事情：
这将调用并从数据库中检索记录，然后尝试对它们进行计数。
It may be how you are using LINQ. if you call the query like this:
That would effectively only call a query to return a count from the database. If you did something like this:
That would call and retrieve the records from the database then try to count them.
让数据库告诉您计数 - 数据库的构建就是为了能够执行此操作 - 并且仅从数据库中选择您需要的行，而不是当您只想使用一小部分时从数据库返回整个集合。
let the database tell you the count - databases are built to be able to do this - and select only the rows you need from the database instead of returning the whole set from the database when you only want to use a small subset.
您可以先执行选择计数查询，但随后可能会添加或删除一些数据。将其包装在事务中，您将遇到巨大的锁定问题。
另一种方法是获取第 1 页，然后在另一个线程上批量获取其余部分，当完成更新计数和显示时，您甚至可以有一个运行计数，以指示获取所有数据的进度。
为什么要一次性获取所有记录？
如果你把它看作获取第一页、最后一页、上一页和下一页。
然后处理该方法产生的问题，您就会得到一个可扩展的解决方案。
count 花费这么长时间的原因是它必须将所有 10,000 条记录吸入客户端才能对其进行计数。
这是一个扩展性非常差的解决方案，想象一下 100,000 个或 100 万个！
另一点是延迟。这 100,000 个是执行查询时的数据，刷新不会流行，是吗？然后，当您刷新时，您将显示您所在页面的所有内容...
如果您重新设计它以获取当前显示的最后一个记录之后或第一个记录之前的下一个 n 记录...
然后你可以缓存，你可以在后台刷新页面，你可以尝试预测请求，比如提前获取接下来的 n 个页面。
You could do a select count query first, but then some data might have been added or deleted. Wrap it in a transaction, and you'll have a huge locking issue.
Another way would be to get page 1, and then get the rest in batches on another thread, when that finishes update the count and display, you could even have a running count, to indicate progress in getting all the data.
Why are you getting all the records at once?
If you looked at it as getting the first, last, previous and next page.
And then dealt with issues that arise from that approach, you'd have a scalable solution.
The reason count is taking so long is it has to suck all 10,000 records into the client in order to count them.
It's a solution that will scale very badly, imagine a 100,000 or a million!
The other point is latency. Those 100,000 are at the time the query was executed, refresh isn't going to be popular is it. Then when you do refresh, you have all the display the page you were on issues...
If you reworked it to get the next n records after the the last one currently displayed or before the first...
Then you could cache, you could refresh pages in the background, you could attempt to predict request, say get the next n pages in advance.