Ordering by a computed column

[kieranbenton]

Hi, Found my way here via https://github.com/dotnet/efcore/issues/20967#issuecomment-983857486 and frankly your project is brilliant and has solved our pagination woes in a repeatable and consistent way.

I have one corner case that I was hoping to discuss how it might be solved (I can attempt a PR for it if necessary). We would like to be able to order by an EF Core 'computed column'. Something like:

    [NotMapped]
    public short status_order { get; set; }

            modelBuilder.Entity<OurTable>()
                .Property(x => x.status_order)
                .HasComputedColumnSql(@"
                    CASE status
                        WHEN 0 THEN 0
                        WHEN 3 THEN 1
                        WHEN 2 THEN 2
                        WHEN 1 THEN 3
                        WHEN 99 THEN 99
                        ELSE -1
                    END
                ");

            var keysetContext = ourTable
                .KeysetPaginate(
                    b => b.Ascending(p => p.status_order),
                    KeysetPaginationDirection.Forward,
                    reference 
                );

                        var items = await paginationContext
                .Query
                .Take(pageSize)
                .ToListAsync(cancellationToken);

Right now this builds a SQL query with an ORDER BY clause that directly refers to the computed (not actually existing) column:

...
ORDER BY e.status_order

Which obviously does not work.

So initially by query is two-fold:

1. Is this even possible at all right now with EF Core?
2. If yes, can you give me some pointers as to how you might go about attacking this so I can have a go myself?

[mrahhal]

Hi. Glad it was of help.

So, for the query to work, the generated sql shouldn't be qualifying it with e.. This library only dynamically creates the comparison expressions and feeds them back to EF, it's not involved in translating anything, so the problem here is from EF itself most likely.

Does this work if you don't use KeysetPaginate at all (and just use an OrderBy directly) or does the same thing happen? If the same happens, that means maybe it's an EF bug? It seems to me this should work as is from the code you've shown, so I feel it's an EF translation bug.

[kieranbenton]

You're right, dropping MR.EntityFrameworkCore.KeysetPagination out completely and doing an OrderBy through EF Core generates the 'bad' SQL against the non-existent column - thanks for the pointer!