CODEWITHSUNDEEP
c#linq
subash
subash

Reputation: 4137

linq query to get all child elements by passing parent id

i have a agency table structure like below in sql

AGENCY_ID   AGENCY_NAME PARENT_AGENCY_ID
7              yyyyy    2
8               xxxx    0
9               aaaa    1
6               bbbb    0
1               cccc    7
2              ddddd    0
3              eeeee    1
4              fffff    2
5               ggggg   9

i want a LINQ query to get result set in such away that when i pass a agency id i should get all the sub agencies derived from the agency

for instance if i pass parameter agency id as "7", then i should get all childagencies ,grandchild agencies, great grandchild agencies and so on ,derived from the agency id 7 .For the agency id as "7" the result set would look like below

AGENCY_ID   AGENCY_NAME PARENT_AGENCY_ID
    7          yyyyy    2      
    9          aaaa     1    
    1          cccc     7       
    3          eeeee    1    
    5          ggggg    9

how can i achieve this in linq query?

Upvotes: 1

Views: 1643

Answers (2)

James
James

Reputation: 82096

This is going to require a bit of recursion, which isn't generally optimal, adding a layer of LINQ on top of that is potentially going to make it even slower. My advice would be do this directly in SQL and expose it as an SP which you can call from your application code.

In terms of what your SP would look like, this can be achieved using a CTE e.g.

CREATE PROCEDURE GetAssociatedAgencies
(
    @AgencyID int
)
AS
BEGIN
    SET NOCOUNT ON;
    WITH cte AS
    ( 
        SELECT T1.* FROM Agencies as T1
        WHERE AGENCY_ID = @AgencyID
        UNION ALL
        SELECT T2.* FROM Agencies as T2
        INNER JOIN cte AS C on T2.PARENT_AGENCY_ID = C.AGENCY_ID
    )
    SELECT * FROM cte;
END

Stored procedures are generally added as a method on your context so this will be as simple as calling

using (var context = new MyDataContext())
{
    var results = context.GetAssociatedAgencies(7);
    // query results
}

See it in action.

Upvotes: 1

Eren Ersönmez
Eren Ersönmez

Reputation: 39085

You write recursive lambda expressions in the following way, and utilize LINQ:

public class Agency
{
    public int Id {get; set;}
    public int ParentId {get; set;}
    public string Name {get; set;}
}

void Main()
{
    var list = new List<Agency> {
        new Agency { Id = 7, ParentId = 2},
        new Agency { Id = 8, ParentId = 0},
        new Agency { Id = 9, ParentId = 1},
        new Agency { Id = 6, ParentId = 0},
        new Agency { Id = 1, ParentId = 7},
        new Agency { Id = 2, ParentId = 0},
        new Agency { Id = 3, ParentId = 1},
        new Agency { Id = 4, ParentId = 2},
        new Agency { Id = 5, ParentId = 9}      
    };

    Func<Agency,int, bool> isParent = null;
    isParent = (a,i) =>  a != null && 
       (a.Id == i || isParent(list.FirstOrDefault(x => x.Id == a.ParentId),i)); 
    var descendantsOf7 = list.Where(x=>isParent(x,7)).ToList();
}

However, it probably would be more readable and simpler to write a recursive method that does the same thing.

Upvotes: 1

Related Questions