Menu
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.
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] 
536
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
CTE was introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE a view, as part of the view's SELECT query.
CTEs, like database views and derived tables, enable users to more easily write and maintain complex queries via increased readability and simplification. This reduction in complexity is achieved by deconstructing ordinarily complex queries into simple blocks to be used, and reused if necessary, in rewriting the query.
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
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.
How to: Directly Execute SQL Queries (LINQ to SQL)
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
