D 2019-04-02T11:52:03.600
J foundin Sqlite\s3.25\sand\snewer
J icomment Sqlite\s3.25\sintroduced\swindowing\sfunctions,\showever\sthey\sare\snot\susable\svia\sEF6\swith\sSqlite.\r\n\r\nusing\sEF6\swith\sSqlite\syou\scannot\suse\sSkip()\sat\sall\sand\scannot\suse\sGroupBy()\sin\sa\sway\sthat\swould\srequire\swindowing\sfunctions,\sas\sthey\sresult\sin\sa\sNotSupportedException.\r\n<br><br>\r\nFor\sexample,\s<pre>Table.OrderBy(t\s=>\st.colname).Skip(50)</pre>\scould\sbe\stranslated\sto\r\n\r\n<pre>\r\nselect\s*\r\nfrom\s(select\st.*,\r\n\s\s\s\s\s\s\s\s\s\s\s\s\srow_number()\sover\s(order\sby\st.colname)\sas\srownum\r\n\s\s\s\s\s\sfrom\stable\st)\r\nwhere\srownum\s>\s50\r\n</pre>\r\n<br><br>\r\nComplex\sgroupings\scan\sbe\ssolved\swith\swindow\sfunctions\sas\swell,\sfor\sexample\r\n<pre>Table.GroupBy(t\s=>\st.col1).Select(g\s=>\sg.OrderBy(t\s=>\st.col2).First())</pre>\r\ncan\sbe\stranslated\sto\r\n<pre>\r\nselect\st.*\r\nfrom\stable\st\r\njoin\s(\sselect\s*,\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\srow_number()\sover\s(partition\sby\scol1\sorder\sby\scol2)\srownum\r\n\s\s\s\s\s\s\sfrom\stable\s)\sx\son\st.id\s=\sx.id\r\nwhere\sx.rownum\s=\s1\r\n</pre>\r\n<br><br>\r\nwhen\susing\sEF6\swith\ssql\sserver\sthese\stypes\sof\slinq\sfuntions\swould\sproduce\squeries\sthat\suse\swindowing\sfunctions\s(mostly\srow_number).\sIt\swould\sbe\sgreat\sif\sSqlite\scould\sdo\sthis\stoo,\sat\sleast\sfor\sthe\srow_number\swindow.\sPerhaps\syou\scould\smodel\sit\soff\sthe\ssql\sserver\sEF6\sprovider,\sif\sthat\sis\sat\sall\spossible,\ssince\sit\sseems\sto\sbe\sable\sto\shandle\sextremely\scomplex\slinq\squeries\svery\swell.
J login anonymous
J mimetype text/x-fossil-wiki
J private_contact eccae9b092ba609db7fcd6d3f1592dcdd0c6b35e
J severity Important
J status Open
J title Support\sfor\swindowing\sfunctions\sin\sSqlite\sEF6
J type Feature_Request
K 8f05349da78773f4dfc29ace794ad174b909917b
U anonymous
Z e90ccfdd97abd0c0a2bbd4f46e431307