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:

1. Linq to SQL
2. Entity Framework
3. Dynamic Linq
4. SQLFunctions class
5. Manual TSQL via a SQLCommand

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

Answer 1

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

Answer 2

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()
    };