Support for windowing functions in Sqlite EF6
|User & Date:||anonymous 2019-04-02 11:52:03|
- Change foundin to "Sqlite 3.25 and newer"
- Change icomment to:
Sqlite 3.25 introduced windowing functions, however they are not usable via EF6 with Sqlite.
using EF6 with Sqlite you cannot use Skip() at all and cannot use GroupBy() in a way that would require windowing functions, as they result in a NotSupportedException.
Table.OrderBy(t => t.colname).Skip(50)could be translated to
select * from (select t.*, row_number() over (order by t.colname) as rownum from table t) where rownum > 50
Complex groupings can be solved with window functions as well, for example
Table.GroupBy(t => t.col1).Select(g => g.OrderBy(t => t.col2).First())can be translated to
select t.* from table t join ( select *, row_number() over (partition by col1 order by col2) rownum from table ) x on t.id = x.id where x.rownum = 1
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.
- Change login to "anonymous"
- Change mimetype to "text/x-fossil-wiki"
- Change private_contact to "eccae9b092ba609db7fcd6d3f1592dcdd0c6b35e"
- Change severity to "Important"
- Change status to "Open"
- Change title to "Support for windowing functions in Sqlite EF6"
- Change type to "Feature_Request"