EntityFramework6 / Npgsql: how to translate orderBy nulls last into SQL query

Question

I am using EntityFramework6.Npgsql and am working with tables with several million records. Some of the entities have a timestamp / DateTime column which allows null values. Users often need to sort on this column and would like to show null values last in both asc and desc sorting. The column has two indizes for both sorting types with both of them set to nulls last

I know in SQL I can add order by "DateColumn" desc nulls last and postgres uses the appropriate index and the query returns (paged, e.g. 25 records) results in a few milliseconds.

With EntityFramework however, in order to sort descending with nulls last, I have to add something like this:

entities.MyEntity
    .OrderByDescending(e => e.DateColumn.HasValue)
    .ThenByDescending(e => e.DateColumn)

This however translates to a query that contains something like this for sorting:

SELECT 
    CASE
        WHEN ("Extent1"."DateColumn" IS NOT NULL) THEN (TRUE)
        ELSE (FALSE)
    END AS "C1"

...

ORDER BY "C1", "DateColumn" DESC

Which works as intended but the query takes several seconds (instead of several milliseconds) to execute, as it is unable to use the index.

I tried adding an index to the column with that same expression, but postgres does not seem to use the index when executing the query.

What options do I have to write a query in EntityFramework / Npgsql that can translate into the desired SQL (order by "DateColumn" desc nulls last)? Is there a way to intercept/overwrite how Linq queries are translated into SQL?

forrert forrert · Accepted Answer · 2018-02-20T23:52:36

Answering my own question...

While still unable to have Linq generate an SQL query that sorts with nulls last, I was able to create a combined index for the null-check and the date column itself, that is being used by postgres:

CREATE INDEX "MyTable_IX_DateCombined"
  ON "MyTable"
  USING btree
  ((
  CASE
    WHEN "DateColumn" IS NOT NULL THEN true
    ELSE false
  END) DESC, "DateColumn" DESC);

Since I'd have to create an additional index in either case, I consider this solution good enough at this point.

EntityFramework6 / Npgsql: how to translate orderBy nulls last into SQL query

1 Answers