C#代码中的SQL Lead和Lag函数 [英] SQL Lead and Lag functions from C# code

问题描述

是否可以使用C#中的 LEAD 或 LAG SQL函数?

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

我对方法的偏爱是:

1. Linq to SQL
2. 实体框架
3. 动态Linq
4. SQLFunctions类
5. 通过SQLCommand手动进行TSQL

我知道可以通过方法5来完成，但是1到4呢?

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

推荐答案

查看MoreLinq项目(在github上): http://morelinq.github.io

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

在那里，Lead和Lag被实现为扩展:

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
)

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

这仅是对对象的Linq.因此，当应用于SQL数据源时，它将获取所有行，然后在数据库外部进行计算.这不是OP所期望的.

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.

对于项目1,2,3和4，研究结果说"否，这是不可能的":

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

• LEAD 和 LAG 出现在SQL Server 2012中，但最新版本的Linq to SQL(Framework 4.6.1)的目标是最高版本的SQL Server.版本特定的代码是2008年: http://referencesource.microsoft.com/#System.Data.Linq/SqlClient/SqlProvider.cs，2fac3481a656764b
• 实体框架:不，对不起.
• MSDN提示序列函数通常具有有限的支持: https://msdn.microsoft.com/de-de/library/bb882656(v = vs.100).aspx
• 没有暗示 SqlFunctions 将提供Lead，Lag或类似内容:

• 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://msdn.microsoft.com/de-de/library/bb882656(v=vs.100).aspx
• there is no hint that SqlFunctions would provide Lead, Lag, or something similar: https://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions(v=vs.110).aspx

这篇关于C#代码中的SQL Lead和Lag函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！