Why does orderBy in F# not result in an ORDER BY in SQL?

I’m currently working on a small project that uses USA county data. I have no problems ordering the data in a Seq.orderBy, but as there is a sortBy in the query expression I would expect the results to be sorted. This is not the case.

type SysData = SqlDataConnection<"Data Source=ROMESQLEXPRESS;Initial Catalog=SysData;Integrated Security=True">
type County = { State : string; SSA : string }

let counties =
    let db = SysData.GetDataContext()
    query {
        for c in db.CountyMatrix do
        sortBy c.Countyssa
        select { State = c.State; SSA = c.Countyssa }
        distinct
    }

Now, the above is what I’m executing, but my results end up looking like so:

...
{State = "OR";
 SSA = "38030";}
{State = "GA";
 SSA = "11630";}
{State = "WA";
 SSA = "50130";}
{State = "MN";
 SSA = "24740";}
{State = "KY";
 SSA = "18030";}
{State = "MO";
 SSA = "26970";}
{State = "DC";
 SSA = "09000";}
...

And the query sent to my local SQL Server instance is displayed in IntelliTrace as so:

USE [SysData];

GO

SELECT DISTINCT [t0].[state] AS [Item1], [t0].[countyssa] AS [Item2]
FROM [dbo].[CountyMatrix] AS [t0]

Note the lack of an ORDER BY, which I was expecting to be there because of the query expression’s sortBy c.Countyssa.

Any ideas as to why I’m not getting sorted data out of counties? I’m aiming to make this as clean as possible to show as a small example to my employer.

Thanks in advance!

Answer

The order of the keywords is important. If you read the description carefully, then SortBy will sort the elements that have been selected so far. Distinct will remove duplicates so far (but there is nothing in there about keeping order), etc. Each of these keywords acts on the one before it, but keep in mind that it may also undo something that was done before (like the sorting).

See the description of each of these keywords here.

for c in db.CountyMatrix do 
    let c = { State = c.State; SSA = c.Countyssa } 
    distinct 
    sortBy c.SSA 
    select c

You may need to use a temporary variable to catch the data type in order to distinct and sort them.