System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Overview

Artifact ID: e0d2b82850f40f02e0860be47a6ac34bf7afbb61
Ticket: 8f05349da78773f4dfc29ace794ad174b909917b
Support for windowing functions in Sqlite EF6
User & Date: anonymous 2019-04-02 11:52:03
Changes

  1. foundin changed to: "Sqlite 3.25 and newer"
  2. icomment:
    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.
    <br><br>
    For example, <pre>Table.OrderBy(t => t.colname).Skip(50)</pre> could be translated to
    
    <pre>
    select *
    from (select t.*,
                 row_number() over (order by t.colname) as rownum
          from table t)
    where rownum > 50
    </pre>
    <br><br>
    Complex groupings can be solved with window functions as well, for example
    <pre>Table.GroupBy(t => t.col1).Select(g => g.OrderBy(t => t.col2).First())</pre>
    can be translated to
    <pre>
    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
    </pre>
    <br><br>
    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.
    
  3. login: "anonymous"
  4. mimetype: "text/x-fossil-wiki"
  5. private_contact changed to: "eccae9b092ba609db7fcd6d3f1592dcdd0c6b35e"
  6. severity changed to: "Important"
  7. status changed to: "Open"
  8. title changed to: "Support for windowing functions in Sqlite EF6"
  9. type changed to: "Feature_Request"