Sort fields in database with linq

I 开发者_如何学编程need to sort query results in the actual database (so I don't have to sort it everytime when I get some results). Can I do this with linq?

Database tables are inherently unordered; this is not possible.

Instead, you can use a view with an ORDER BY clause, or create a property in your data-access class that returns an IOrderedQueryable<T>.

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.

Create proper indexes, create proper query and everything will be fine.

Even if the data is "presorted" - dbms doesn't know about that and it will sort the data anyway (even though it has already been sorted). And there is no guarantee that while performing the query database will read the data sequentially.

Tables in SQL has no notion of being sorted. The closest you can get is creating a view on the SQL server and query that view with LINQ2SQL.

With proper indexes the speed will be good and especially you don't need to remember sorting when querying.