2

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:

  1. It should search for all accounts with the given id
  2. Search all parents (recursive) for the accounts found in step 1
  3. 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.

  • You could select the Id and ParentId until you see no further change in the result (while or do while loop) and then select all entries based on that id's. But you will have lots of roundtrips to your database. Currently EF does not Support CTEs. – TGlatzer May 21 '15 at 08:48
  • possible duplicate of [Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax](http://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax) – TGlatzer May 21 '15 at 08:49
  • You want to join the accounts where AccountId = ParentAccountID. – jdweng May 21 '15 at 09:28
  • What you think about http://www.sqltolinq.com/ ? – Boris Gappov May 21 '15 at 12:44
  • Add this procedure to data context and call it in linq quey or directly like this: var items = sp_GetContactsForCompany(guid); Or create view using this query import it into data context. – Boris Gappov May 21 '15 at 13:40
  • You have to use AsHierarchy() method. Check this article: [http://www.scip.be/index.php?Page=ArticlesNET18#AsHierarchy](http://www.scip.be/index.php?Page=ArticlesNET18#AsHierarchy) – Adrian Stanculescu Jul 08 '15 at 14:32

0 Answers0