当前位置：文江博客话题详情

使用 linq 对数据库中的字段进行排序

发布于 2024-10-26 09:43:52 字数 68 浏览 1 评论 0原文

我需要在实际数据库中对查询结果进行排序（这样我就不必每次得到一些结果时都对其进行排序）。我可以用 linq 做到这一点吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怼怹恏 2024-11-02 09:43:52

数据库表本质上是无序的；这是不可能的。

相反，您可以使用带有 ORDER BY 子句的视图，或者在数据访问类中创建一个返回 IOrderedQueryable 的属性。

回复收藏 0 原文

一梦等七年七年为一梦 2024-11-02 09:43:52

我暂时（暂时）不同意人们所说的表格未排序的观点。通常情况下，如果您在表上有聚集索引（在具有这种概念的数据库中）或者您在索引上聚集了表（在具有这种不同但相关概念的数据库中），那么就会出现“无序”选择按该顺序检索结果。

不过，我的分歧只是暂时的，因为他们说的是事情的真相。事实上，无序选择按该顺序获取结果纯粹是因为它将是数据库最快的顺序。还值得注意的是，数据库将足够智能，如果您有一个按 id 排序的表，并且您要求它选择按 id 排序的它们，它就会知道不必重新排序。

因此，理想情况下，应该在表最常被选择的一列或多列上进行聚类（但请注意，这包括在查询中将一个表连接到另一个表时使用的内部选择，因此通常主键是最好的选择）集群，即使它从未构成查询排序的基础）。

需要特定顺序的查询应始终明确要求该顺序，即使这看起来很浪费，因为无序版本最终以相同的顺序结束 - 您不想指望数据库最有效的方式稍后再采取行动，而且你不会因为直白而失去任何东西。

LINQ 是否对数据库进行排序取决于它获取结果的方式。假设 MyTable 是某种 TEntity 的 System.Data.Linq.Table 类型的变量。然后：

from m in MyTable orderby m.Id select new {m.Id, m.Name}

将对数据库进行排序，将其转换为类似的内容

SELECT Id, Name FROM MyTable ORDER BY MyTable.Id

，然后在流从数据库传入时发出每个对象。但是：

from m in MyTable.AsEnumerable() orderby m.ID select new {m.Id, m.Name}

首先将执行相当于：

SELECT * FROM MyTable

然后在内存中对它们进行排序，然后在匿名对象可用时创建它们（以及其他明显的缺点，即使结果以相同的顺序排列，现在也会完成排序，如下所示）数据库没有相同的先验知识可以用来在可能的情况下跳过排序，并且使用某些算法对已排序的数据进行排序会产生更差的性能）。

在这些情况下，前者显然优于后者，但这是一个相当简单的情况。在更复杂的情况下执行后一种形式可能是必要的或至少是期望的，并且当处理更复杂的情况时，也可能意外地在不期望的情况下执行后一种情况的等价形式。值得尝试在您头脑中的程序员分析部分保持良好的了解，了解 Linq 何时处理数据库中的内容以及何时处理内存中的内容。通过 SQL Profiler 和/或在 LinqPad 中测试查询来检查为更复杂的查询生成的 SQL 通常是一个好主意。

I'm going to disagree slightly (temporarily) here with the people saying that tables aren't sorted. Often they are and if you have a clustered index on a table (in databases with such a notion) or you have clustered a table on an index (in databases with this different-but-related notion) then an "unordered" select is going to retrieve the results in that order.

However, my disagreement is only temporary, because what they said is the real truth of the matter. The fact that an unordered select obtains results in that order is purely because it will be the database's fastest order to put them in. It's worth noting also, that the database will be smart enough that if you have a table ordered by id, and you ask for it to select them ordered by id, it will know that it doesn't have to re-order them.

Clustering should therefore ideally be done on whichever column or columns a table will most frequently be selected by, (but note that this includes the internal selects used when joining one table to another in a query, so very often the primary key is the best to cluster on, even if it never forms the basis of the ordering of your queries).

Queries that require a certain order, should always explicitly ask for that order, even if it seems wasteful because the unordered version ends up in the same order - you don't want to bank on what happens to be the most efficient way for the DB to act to be so later, and you don't lose anything from being explicit.

Whether LINQ does the ordering on the database or not depends on the way it is obtaining the results. Lets assume MyTable is a variable of type System.Data.Linq.Table<TEntity> for some sort of TEntity. Then:

from m in MyTable orderby m.Id select new {m.Id, m.Name}

will do the ordering on the database, turning it into something like

SELECT Id, Name FROM MyTable ORDER BY MyTable.Id

and then emit each object as the stream comes in from the database. However:

from m in MyTable.AsEnumerable() orderby m.ID select new {m.Id, m.Name}

will first do the equivalent of:

SELECT * FROM MyTable

then order them in memory, and then create the anonymous objects as they then become available (along with other obvious disadvantages, the ordering will now be done even if the results come in the same order, as there isn't the same prior-knowledge a database can use to skip the ordering when possible, and with some algorithms ordering already-ordered data has worse-case performance).

In these cases the former is clearly superior to the latter, but this is a rather simple case. It can be both necessary or at least desirable to do the latter form in more complicated cases, and it can also be possible to accidentally do the equivalent of the latter case where not desirable, when dealing with more complicated cases. It's worth trying to keep a good view in the programmer-analytic part of your mind, of when Linq will deal with something in the DB and when in memory. Examining the SQL produced for your more complicated queries is often a good idea either through SQL Profiler and/or testing the query in LinqPad.

回复收藏 0 原文