我得到了这样的SQL代码,我将在。net应用程序中使用,我熟悉LINQ,但我不知道如何处理with语句。
WITH records
AS
(
SELECT [key], [rev], [IsCurrent],
ROW_NUMBER() OVER(PARTITION BY [key]
ORDER BY CASE WHEN [rev] = @revNUm
THEN 0 ELSE 1 END) rn
FROM tableName
WHERE [Rev] IN (@revNUm,0)
)
SELECT [key], [rev], [IsCurrent]
FROM records
WHERE rn = 1
编辑:ROW_NUMBER()似乎很难,我怎么做呢?
谢谢
看起来您正在获取rev
为0或等于revnum
参数的每个密钥组中的第一个记录。你可以在Linq中这样做:
var query = db.records
.OrderBy(r => r.rev == revNum ? 0 : 1)
.Where(r => rev == 0 || rev == revNum)
.GroupBy(r => r.key)
.Select(g => g.First());
如果记录包含的字段多于查询中的三个字段,则只需添加
.Select(r => new {r.key, r.rev, r.IsCurrent})
将查询保存为存储过程,并使用LINQ调用存储过程(参见这里:http://msdn.microsoft.com/en-us/library/bb918119.aspx)。像这样:
CREATE PROCEDURE newprocedure
@revNUm INT
AS
BEGIN
WITH records
AS
(
SELECT [key], [rev], [IsCurrent],
ROW_NUMBER() OVER(PARTITION BY [key]
ORDER BY CASE WHEN [rev] = @revNUm
THEN 0 ELSE 1 END) rn
FROM tableName
WHERE [Rev] IN (@revNUm,0)
)
SELECT [key], [rev], [IsCurrent]
FROM records
WHERE rn = 1
END
从代码中创建内联表值函数
CREATE FUNCTION YourFunction
(
)
RETURNS TABLE
AS
RETURN
(
WITH records
AS
(
SELECT [key], [rev], [IsCurrent],
ROW_NUMBER() OVER(PARTITION BY [key]
ORDER BY CASE WHEN [rev] = @revNUm
THEN 0 ELSE 1 END) rn
FROM tableName
WHERE [Rev] IN (@revNUm,0)
)
SELECT [key], [rev], [IsCurrent]
FROM records
WHERE rn = 1
)
GO
将此函数拖放到DBML设计器中。这样,您将获得最快的执行时间和最简单的实现方式。