EF Core 3, OrderByDescending and ThenBy rerurn error could not be translated

Question

• EF Core 3.1.8
• Npgsql.EntityFrameworkCore.PostgreSQL 3.1.4

I implemented following query which must retirn all comment's group by date and after time.

var commentsOnPage = await _repository.GetAll()
                .OrderByDescending(c => c.Date.Date)
                .ThenBy(c => c.Date.TimeOfDay)
                .Skip(pageSize * pageIndex)
                .Take(pageSize)
                .ToListAsync();



 public class CommentRepository : GenericRepository<Comment>, ICommentRepository
    {
        private readonly AppDbContext _dbContext;

        public CommentRepository(AppDbContext dbContext) : base(dbContext)
        {
            _dbContext = dbContext;
        }

        public IQueryable<Comment> GetAll() => ((AppDbContext)_dbContext).Comments.AsQueryable();

    }

Return error:

An unhandled exception has occurred while executing the request.
System.InvalidOperationException: The LINQ expression 'DbSet<Comment>
    .OrderByDescending(c => c.Date.Date)
    .ThenBy(c => c.Date.TimeOfDay)' could not be translated. 

Answer 1

One of the current EF Core query translation defects is the lack of documentation of what is supported. To make the things harder, database providers are allowed to add translations to some CLR properties/methods they decide to, so even some LINQ query translates for one database provider, it could fail for another.

In this particular case, the unsupported member is TimeOfDay. SqlServer provider supports it, but Npgsql doesn't. At least Npgsql has provided documentation for supported translations which shows that TimeOfDay is missing (not supported).

From the other side, Npgsql supports DateTime substract operator (but SqlServer doesn't), so one way to resolve the issue for Npgsql is to calculate TimeOfDay by subtracting date portion from the datetime value

.ThenBy(c => c.Date - c.Date.Date)

Another way which would achieve the goal for this particular case (and should work with any provider) is to simply use the datetime value

.ThenBy(c => c.Date)

It's because ThenBy is effect only for equal values in OrderBy. Since you are first ordering by date part (w/o time), then for equal dates you could simply order by datetime, which would have the same effect as the time of day.