0

I created a query for a Month-Over-Month Sales report using the LAG function in SQL. I discovered that is not available in linq. Is there a way to still accomplish this?

stuartd
  • 70,509
  • 14
  • 132
  • 163
Nyssa Morrell
  • 1
  • 1
  • The only way is to send the raw SQL query using `LAG()` to the server (or call a SP containing it), then process the results. – stuartd Apr 30 '19 at 15:58
  • 1
    You could use a self-join, or if you are filtering your query sufficiently before querying, or if your tables are sufficiently small, you could bring over the entire table and use LINQ to Objects to handle the query on the client side. – NetMage Apr 30 '19 at 21:54

1 Answers1

1

LinqTo2Db supports Window-Functions Lag and Lead:

from x in db.Events
let prev = Sql.Ext
                .Lag(x.app, Sql.Nulls.None)
                .Over()
                .OrderBy(x.time)
                .ToValue()
where  (prev != x.app && prev != null)
select (prev, x.app)

First 8 of all 43 supported funcs (!)

|-------------|----------------------| 
|    SQL      |       Linq2db        |
|-------------|----------------------| 
| AVG         | Sql.Ext.Average()    |
| CORR        | Sql.Ext.Corr()       |
| COUNT       | Sql.Ext.Count()      |
| COVAR_POP   | Sql.Ext.CovarPop()   |
| COVAR_SAMP  | Sql.Ext.CovarSamp()  |
| CUME_DIST   | Sql.Ext.CumeDist()   |
| DENSE_RANK  | Sql.Ext.DenseRank()  |
| FIRST_VALUE | Sql.Ext.FirstValue() |
|-------------|----------------------|

Note There is no limitation in window functions usage. LINQ To DB will create SQL and run query, if function is not supported or some part of function is limited in particular Database - error will be thrown on database side.

Brains
  • 594
  • 1
  • 6
  • 18
  • hey... I have tried this but I am getting System.NotSupportedException... Any help will be appriciated – Beena Gupta Aug 16 '20 at 07:56
  • Check if your `SQL` provider is supported. Try to simplify your query until it works and then add details gradually to detect what clause or operation causes the error. In general, you should experiment with `window-functions`, there are a lot of subtle details. – Brains Aug 17 '20 at 08:35