SQL Lead and Lag functions from C# code

Question

Is it possible to use the LEAD or LAG SQL functions from C#?

My preference of method is:

Linq to SQL
Entity Framework
Dynamic Linq
SQLFunctions class
Manual TSQL via a SQLCommand

I know it can be done via method 5, but what about 1 through 4?

http://stackoverflow.com/a/21767411/1132334 would imply that 1 and 3 are not possible (yet). But this answer is already more than two years old so research in the most recent .NET framework seems worth the effort — Cee McSharpface, May 23 '16 at 07:29

score 3 · Accepted Answer · edited Oct 25 '21 at 12:58

Look into the MoreLinq project (on github): http://morelinq.github.io

There, Lead and Lag are implemented as extensions:

public static IEnumerable<TResult> Lag<TSource, TResult>(
    this IEnumerable<TSource> source,
    int offset,
    TSource defaultLagValue,
    Func<TSource, TSource, TResult> resultSelector
)

reference: https://morelinq.github.io/2.0/ref/api/html/M_MoreLinq_MoreEnumerable_Lag__2_1.htm

EDIT: This is Linq to Objects only. So when applied to an SQL data source, it would fetch all rows and then do the computation outside the database. This is not what the OP expects.

Research results say "no, it is not possible" for items 1,2,3 and 4:

LEAD and LAG came about in SQL Server 2012, but the highest version of SQL server that the newest version of Linq to SQL (Framework 4.6.1) targets with version specific code, is 2008: http://referencesource.microsoft.com/#System.Data.Linq/SqlClient/SqlProvider.cs,2fac3481a656764b
Entity framework: nope, sorry.
MSDN hints that sequence functions generally have limited support: https://learn.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/bb882656(v=vs.100)
There is no hint that SqlFunctions would provide Lead, Lag, or something similar: https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.sqlclient.sqlfunctions?&view=netframework-4.8

I did look at that, but it appears to be just for linq to objects. I just downloaded the latest beta to prove, and yep, pulls the entire table from SQL before then performing the lag/lead in memory. — OrdinaryOrange, May 23 '16 at 12:33

Brains · Answer 2 · 2019-07-07T09:43:06.307

Awesome lib linq2db https://github.com/linq2db/linq2db supports Window-Functions with LEAD and LAG:

    from p in db.Parent
    join c in db.Child on p.ParentID equals c.ParentID
    select new
    {
        Diff = Sql.Ext
                  .Lag(x.time, Sql.Nulls.None)
                  .Over()
                  .PartitionBy(p.time.Date)
                  .OrderBy(p.time)
                  .ToValue()
    };

SQL Lead and Lag functions from C# code

2 Answers2

Linked