LINQ: Translating a SQL WITH clause to LINQ and Entity Framework

Question

I have an app using Entity Framework. I want to add a tree view listing products, grouped by their categories. I have an old SQL query that will grab all of the products and categories and arrange them into parent nodes and children. I am trying to translate it into LINQ that uses the EF. But the SQL has a WITH sub-query that I am not familiar with using. I have tried using Linqer and LinqPad to sort it out, but they choke on the WITH clause and I am not sure how to fix it. Is this sort of thing possible in LINQ?

Here is the query:

declare @id int
set @id=0
WITH ChildIDs(id,parentid,type,ChildLevel) AS 
(
SELECT id,parentid,type,0 AS ChildLevel
FROM dbo.brooks_product 
WHERE id = @id
UNION ALL
SELECT e.id,e.parentid,e.type,ChildLevel + 1
FROM dbo.brooks_product AS e
    INNER JOIN ChildIDs AS d
    ON e.parentid = d.id 
   WHERE  showitem='yes' AND tribflag=1
)
SELECT ID,parentid,type,ChildLevel 
FROM ChildIDs
WHERE type in('product','productchild','productgroup','menu')
ORDER BY ChildLevel, type
OPTION (MAXRECURSION 10);

When I run the query, I get data that looks like this (a few thousand rows, truncated here):

ID.....parentid.....type.....ChildLevel
35429..0............menu.....1
49205..0............menu.....1
49206..49205........menu.....2
169999.49206........product..3
160531.169999.......productchild..4

and so on.

I'm don't think it's possible to do that. Generally, I don't see how you could cleanly do recursive queries in LINQ. You can always use raw SQL if you want and EF will just map the results to specified type (a'la Dapper). — Patryk Ćwiek, Mar 14 '14 at 14:16

score 2 · Answer 1 · answered Mar 14 '14 at 14:42

The WITH block is a Common Table Expression, and in this case is used to create a recursive query.

This will be VERY difficult in Linq as Linq doesn't play well with recursion. If you need all of the data on one result set that a Stored Procedure would be easier. Another option is to do the recursion in C# (not in Linq but a recursive function) and do multiple round-trips. The performance will not be as good but if you result set is small it may not make much difference (and you will get a better object model).

STLDev · Answer 2 · 2014-03-14T20:07:07.510

You may be able to solve this using LINQ to Entities, but it is non-trivial and I suspect it will be very time consuming.

In situations like this, you may prefer to build a SQL View or Table-Valued Function that returns the results for which you're looking. Then import that View or Table-Valued Function into your EF model and you can pull data directly from it using LINQ.

Querying the View in LINQ is no different than querying a table.

To get data from a Table-Valued Function in LINQ, you pass the function's parameters in after the name of the function, like so:

var query = from tvf in _db.MyTableValuedFunction(parameters)
            select tvf;

EDIT

As suggested by @thepirat000, Table-Valued Function support is not available in Entity Framework versions prior to version 5. In order to use this functionality, EF must be running with .NET 4.5 or higher.

Note that TVF support was introduced in Entity Framework version 5. Also you must target .NET Framework 4.5. — thepirat000, Mar 14 '14 at 15:15

score 1 · Accepted Answer · answered Apr 08 '14 at 15:18

At the end of the day, I could not get this to work. I ended up writing out a SQL query dynamically and sending that straight to the database. It works fine, and I am not relying on any direct user input so there is no chance of SQL injection. But it seems so old school! For the rest of my program I am using EF and LINQ.

Thanks for the replies!

LINQ: Translating a SQL WITH clause to LINQ and Entity Framework

3 Answers3