LINQ实体框架中的WITH语句



我得到了这样的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设计器中。这样,您将获得最快的执行时间和最简单的实现方式。

相关内容

  • 没有找到相关文章

最新更新