linq-to-sql中的通用表表达式(CTE)? [英] Common Table Expression (CTE) in linq-to-sql?

问题描述

是否可以在Linq to SQL中执行公用表表达式(CTE)(如下所示).我对CTE以及对SQL的Linq都很陌生.

Is it possible to do common table expressions (CTE) (like shown below) in Linq to SQL. I'm pretty new to CTE's as well as Linq to SQL.

我目前免费存储Proc(但绝不反对)，除非完全必要，否则我不想跳到只针对一个查询的存储proc.

I'm currently Stored Proc free (but not against them by any means) so i don't want to take the leap to stored procs just for one query unless it's totally necessary.

这是我在SQL中正在做的事的一个示例，我想知道我是否可以在Linq to SQL中做

Here's an example of what I'm doing in SQL that I'm wondering if I can do in Linq to SQL:

WITH TaskHierarchy (TaskID, [Subject], ParentID, HierarchyLevel, HierarchyPath) AS
(
   -- Base case
   SELECT
      TaskID,
      [Subject],
      ParentID,
      1 as HierarchyLevel,
      CONVERT(VARCHAR(MAX),'/') AS HierarchyPath
   FROM Task
   WHERE TaskID = 2

   UNION ALL

   -- Recursive step
   SELECT
      t.TaskID,
      t.Subject,
      t.ParentID,
      th.HierarchyLevel + 1 AS HierarchyLevel,
      CONVERT(varchar(MAX),th.HierarchyPath + CONVERT(VARCHAR(32),t.ParentID) + '/') AS HierarchyPath
   FROM Task t
      INNER JOIN TaskHierarchy th ON
         t.ParentID = th.TaskID
)

SELECT *
FROM TaskHierarchy
ORDER BY HierarchyLevel, [Subject]

推荐答案

AFAIK，对象模型不支持此功能.但是，LINQ支持执行查询的方法(足够叫DataContext.ExecuteQuery).看起来您可以使用它来调用任意一条SQL，并将其映射回LINQ.由于嵌入了SQL，因此您将不会免费使用SQL，但是您不必使用sproc.

AFAIK, this isn't supported by the object model. However, LINQ supports a method to execute a query (strangly enough called DataContext.ExecuteQuery). Looks like you can use that to call a arbitrary piece of SQL and map it back to LINQ. You won't be SQL free because of the embedded SQL, but you won't have to use a sproc.

如何:直接执行SQL查询(LINQ to SQL)

这篇关于linq-to-sql中的通用表表达式(CTE)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！