Count doesn't work on sorted queries (MSSQL)

[ScRyX]

trafficstars

Description

Trying to get a count from a query that has already been sorted throws an exception.

Repro steps

Let's have a sorted query:

let q = query {
    for x in ctx.myEntity do
    sortBy x.sortField
    select x
}

Now let's try to get the length of the result set:

let n = query {
    for _ in q do
    count
}

this throws an SqlException with the message:

Column "dbo.myEntity.sortField" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

this is because the resulting SQL query is: SELECT COUNT(1) FROM [dbo].[MyEntity] as [x] ORDER BY [x].[sortField] DESC

this has nothing to do with us using an anonymous variable in the count query above, because the same SQL is generated for the following:

let n = query {
    for x in q do
    select x
    count
}

Expected behavior

Count works also for queries containing an orderBy clause

Actual behavior

SqlException is thrown:

Column "dbo.myEntity.sortField" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Known workarounds

N/A

Related information

• Used database: MSSQL
• Operating system: Windows
• .NET Runtime, CoreCLR or Mono Version: .NET Core 2

[Thorium]

What you are trying to do here? I think this is a "problem" (or a feature) of Microsoft SQL Server. So that you wouldn't execute the query multiple times by accident.

When you have executed the query, you can get the count/length from the normal data structure without a query syntax:

let myQuery = query {
    for x in ctx.myEntity do
    sortBy x.sortField
    select x
} |> Seq.toArray

let executed =  myQuery  |> Seq.toArray

let mycount = executed |> Array.length

[ScRyX]

I don't want to evaluate the query, I want the DB to do the counting. I want to obtain the count for paging purposes, and paging needs a sorted query, so the paging function receives a sorted query, which crashes the first step of paging - getting the total length of the data.

Wouldn't it be possible to remove the orderBy clause from the query when generating the query for count? Sorting won't change the number of results....

[Thorium]

So something like this?

let getItems pageIdx =
  let total = query {
    for x in q do
    select x
    count
  }

  if totalcount = 0 then Array.empty, 0
  else

  let pagesize = 10
  let pgitms = pagesize*pageIdx

  let page = query {
    for x in q do
    sortByDescending (x.Created)
    skip (pgitms)
    take (pagesize)
    select x
  } |> Seq.toArray
  page, totalcount

I think the reason SQL Server not supporting this as one query is that sorting the query will significantly slower the query execution. And you have to do the separate query for skip and take anyways, right?