1

I have 2 tables as below:

Parent child relationship (table 1):

SourceId    SourceParentId

1     null

2      1

3      2

4      null


Items (Table 2):

Id       SourceId

1       1

2       1

3       2

4       2

5       3

6       4

How to write a linq query that return me all items based on Source ID? If my input is SourceId = 1, i will get items 1,2,3,4 & 5. If my input for sourceId is 2, i will get 3 items: 3, 4 & 5. If my input for sourceID is 4, it will return me item 6. My parent child is N-level and items can appear at any level. Help :(

Mahesh
  • 8,694
  • 2
  • 32
  • 53
user3082385
  • 93
  • 11
  • Check out this [question](http://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax) about recursive queries and EF. – juharr Mar 08 '15 at 06:57

1 Answers1

0

Here try this

--Variable to hold input value
DECLARE @inputSourceID INT = 1;

--Recursive CTE that finds all children of input SourceID
WITH MyCTE
AS
(
SELECT SourceID,
        SourceParentID
FROM table1
WHERE SourceID = @inputSourceID
UNION ALL
SELECT  table1.SourceID, 
        table1.SourceParentID
FROM table1
INNER JOIN MyCTE 
    ON table1.SourceParentID = MyCTE.SourceID 
)

--Join the CTE with the table2 to find all id
SELECT table2.ID
FROM MyCTE
INNER JOIN table2
ON MyCTE.SourceID = table2.SourceID
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • 1 more Question. This SQL query is working fine. How about writing the same query in linq? I am developing a MVC restful web API using entity framework.Is this possible to write a query in linq to join multiple table in controller? Thanks. – user3082385 Mar 11 '15 at 01:02
  • I'm sorry. I've never used LINQ before. – Stephan Mar 11 '15 at 01:22