I'm stuck on a task where I have to transform a Stored Procedure into a LINQ query.
The model:
- AccountSet: Account table with columns 'AccountId', 'ParentAccountId' (references an 'AccountId') and 'Name'
- ContactSet: Contact table with columns 'ParentCustomerId' (references an Account via 'AccountId')
The Stored Procedure:
- It should search for all accounts with the given id
- Search all parents (recursive) for the accounts found in step 1
- Fetch all contacts that have a ParentCustomerId matching an 'AccountId' found in step 2
CREATE PROCEDURE [dbo].[sp_GetContactsForCompany]
(
@projectid AS UNIQUEIDENTIFIER
)
AS WITH recursion ( AccountId, Name, ParentAccountId )
AS (
SELECT AccountId, Name, ParentAccountId
FROM dbo.AccountBase
WHERE AccountId = @projectid
UNION ALL
SELECT a.AccountId, a.Name, a.ParentAccountId
FROM dbo.AccountBase AS a
INNER JOIN recursion AS b ON a.ParentAccountId = b.AccountId
)
SELECT ContactId, FullName
FROM dbo.ContactBase
WHERE ParentCustomerId IN (
SELECT AccountId
FROM recursion
)
ORDER BY FullName
LINQ:
from a in allAccs
where a.AccountId == id
select a;
This gives me all the accounts with the given id. But now I have no idea how to apply the join and recursion.
Any hint would be great.