C# 使用 Linq 选择前 100 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1296479/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting first 100 records using Linq
提问by Zanoni
How can I return first 100 records using Linq?
如何使用 Linq 返回前 100 条记录？
I have a table with 40million records.
我有一张有 4000 万条记录的表。
This code works, but it's slow, because will return all values before filter:
此代码有效，但速度很慢，因为将在过滤器之前返回所有值：
var values = (from e in dataContext.table_sample
where e.x == 1
select e)
.Take(100);
Is there a way to return filtered? Like T-SQL TOP clause?
有没有办法返回过滤？像 T-SQL TOP 子句？
采纳答案by Jon Skeet
No, that doesn't return all the values before filtering. The Take(100)will end up being part of the SQL sent up - quite possibly using TOP.
不，这不会在过滤之前返回所有值。该Take(100)最终将作为SQL的一部分发送了-很可能使用TOP。
Of course, it makes more sense to do that when you've specified an orderbyclause.
当然，当您指定了一个orderby子句时，这样做更有意义。
LINQ doesn't execute the query when it reaches the end of your query expression. It only sends up any SQL when either you call an aggregation operator (e.g. Countor Any) or you start iterating through the results. Even calling Takedoesn't actually execute the query - you might want to put more filtering on it afterwards, for instance, which could end up being part of the query.
当查询到达查询表达式的末尾时，LINQ 不会执行查询。它仅在您调用聚合运算符（例如Count或Any）或开始遍历结果时发送任何 SQL 。即使调用Take实际上也不会执行查询 - 例如，您可能希望之后对其进行更多过滤，这可能最终成为查询的一部分。
When you start iterating over the results (typically with foreach) - that'swhen the SQL will actually be sent to the database.
当您开始迭代结果时（通常使用foreach） -这就是SQL 将实际发送到数据库的时间。
(I think your whereclause is a bit broken, by the way. If you've got problems with your real codeit would help to see code as close to reality as possible.)
（where顺便说一句，我认为您的条款有点不妥。如果您的真实代码有问题，那么查看尽可能接近现实的代码会有所帮助。）
回答by Robert Harvey
I don't think you are right about it returning all records before taking the top 100. I think Linq decides what the SQL string is going to be at the time the query is executed (aka Lazy Loading), and your database server will optimize it out.
我认为您在前 100 名之前返回所有记录的做法是不正确的。我认为 Linq 在执行查询时决定 SQL 字符串将是什么（又名延迟加载），并且您的数据库服务器将进行优化出来。
回答by twk
Have you compared standard SQL query with your linq query? Which one is faster and how significant is the difference?
您是否将标准 SQL 查询与 linq 查询进行了比较？哪个更快，差异有多大？
I do agree with above comments that your linq query is generally correct, but...
我确实同意上述评论，即您的 linq 查询通常是正确的，但是...
- in your 'where' clause should probably be x==1 not x=1 (comparison instead of assignment)
- 'select e' will return all columns where you probably need only some of them - be more precise with select clause (type only required columns); 'select *' is a vaste of resources
- make sure your database is well indexed and try to make use of indexed data
- 在你的“where”子句中应该是 x==1 而不是 x=1（比较而不是赋值）
- 'select e' 将返回您可能只需要其中一些列的所有列 - 使用 select 子句更精确（仅键入所需的列）；'select *' 是大量资源
- 确保您的数据库索引良好并尝试使用索引数据
Anyway, 40milions records database is quite huge - do you need all that data all the time? Maybe some kind of partitioning can reduce it to the most commonly used records.
无论如何，4000 万条记录数据库非常庞大——您是否一直需要所有这些数据？也许某种分区可以将其减少到最常用的记录。
回答by tvanfosson
I'm going to go out on a limb and guess that you don't have an index on the column used in your where clause. If that's the case then it's undoubtedly doing a table scan when the query is materialized and that's why it's taking so long.
我要冒昧地猜测您的 where 子句中使用的列没有索引。如果是这种情况，那么当查询具体化时无疑会进行表扫描，这就是为什么它需要这么长时间。
回答by Chris Melinn
I agree with Jon Skeet, but just wanted to add:
我同意 Jon Skeet 的观点，但只想补充：
The generated SQL willuse TOP to implement Take().
If you're able to run SQL-Profiler and step through your code in debug mode, you will be able to see exactly what SQL is generated and when it gets executed. If you find the time to do this, you will learn a lot about what happens underneath.
There is also a DataContext.Log property that you can assign a TextWriter to view the SQL generated, for example:
dbContext.Log = Console.Out;
Another option is to experiment with LINQPad. LINQPad allows you to connect to your datasource and easily try different LINQ expressions. In the results panel, you can switch to see the SQL generated the LINQ expression.
生成的 SQL将使用 TOP 来实现 Take()。
如果您能够运行 SQL-Profiler 并在调试模式下逐步执行您的代码，您将能够准确地看到生成了什么 SQL 以及它何时被执行。如果你有时间做这件事，你会学到很多关于下面发生的事情。
还有一个 DataContext.Log 属性，你可以分配一个 TextWriter 来查看生成的 SQL，例如：
dbContext.Log = Console.Out;
另一种选择是试验 LINQPad。LINQPad 允许您连接到数据源并轻松尝试不同的 LINQ 表达式。在结果面板中，您可以切换查看生成 LINQ 表达式的 SQL。
