SQL Function to LINQ to SQL

I want to change the following sql procedure to LINQ to SQL, anyone can help me please.

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

CREATE PROC dbo.ShowHierarchy (@Root int) AS
BEGIN
    SET NOCOUNT ON
    DECLARE @EmpID开发者_C百科 int, @EmpName varchar(30)

    SET @EmpName = (
        SELECT EmpName 
        FROM dbo.Emp 
        WHERE EmpID = @Root)

    PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

    SET @EmpID = (
        SELECT MIN(EmpID) 
        FROM dbo.Emp 
        WHERE MgrID = @Root)

    WHILE @EmpID IS NOT NULL
    BEGIN
        EXEC dbo.ShowHierarchy @EmpID
        SET @EmpID = (
            SELECT MIN(EmpID) 
            FROM dbo.Emp 
            WHERE MgrID = @Root AND EmpID > @EmpID)
    END
END

The most efficient way to do the query in SQL Server would be to use Common Table Expressions (short CTE). Write a stored procedure and call it from Linq2Sql. There is no real efficient way to do it in pure Linq except the way Steven outlined (pull out the employer and then recursively call the function) which will result in a fair amount of db queries.

Try this:

public string[] ShowHierarchy(int employeeId)
{
    using (var db = new YourDataContext("yourConStr"))
    {
        return ShowHierarchyRecursive(db, employeeId, 1)
            .ToArray();
    }
}

private IEnumerable<string> ShowHierarchyRecursive(
    YourDataContext db, int employeeId, int level)
{
    // get the name of employeeId from db

    // yield return that name

    // get the list of people managed by that employeeId

    // foreach employee in that list,
    // call the ShowHierarchyRecursive, and
    // foreach item in the returned list yield return
    // that item.
}