Linq query on EFCore Sqlite does not work

Question

I'm using EFCore SQLite, when run a simple query with Linq, I notice that the query Take().Last() does not return what is expected, sample code:

// There are more than 10000 rows in the table
DbSet<DataEntry> set = ctx.Set<DataEntry>();
// this should return the first 20 rows
var current = set.OrderBy(row => row.Id).Take(20);
// first entry is right
long tStart = current.First().Time;
// This should have returned the last entry in the first 20 entries, but instead, it returned the last entry of the entire data set
long tEnd = current.Last().Time;

// so essentially it looks like this:
set.Last().Time == set.Take(20).Last().Time

It looks Last will return the last entry in the entire backing data set, no matter which query it is based on. How can I fix this?

Answer 1

Tested and reproduced in EF Core 2.0.1, SqlServer, hence this seems to be a current EF Core bug in Last / LastOrDefault general query translation.

The query

var last = set.OrderBy(row => row.Id).Take(20).Last();

is translated as if it was

var last = set.OrderByDescending(row => row.Id).Take(20).First();

which of course is not equivalent and always returns the last record of the whole sequence (Take has no effect).

I would suggest reporting it to the EF Core Issue Tracker.

Meanwhile, as a workaround you could use the ugly explicit equivalent:

var last = set.OrderBy(row => row.Id).Take(20)
    .OrderByDescending(row => row.Id).First();