when using EF6 with sql server these types of linq funtions would produce queries that use windowing functions (mostly row_number). It would be great if Sqlite could do this too, at least for the row_number window. Perhaps you could model it off the sql server EF6 provider, if that is at all possible, since it seems to be able to handle extremely complex linq queries very well.
anonymous added on 2019-04-03 08:59:16:

on further thought, the second example would translate more like this if you were to use sql server EF6: