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

问题描述

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

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.

我目前没有存储过程(但不以任何方式反对他们)所以我不想为了一个查询而跳到存储过程，除非完全有必要.

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屋！