CODEWITHSUNDEEP
c#entity-frameworklinq
Jacobdp
Jacobdp

Reputation: 33

c# linq traverse hierarchy data from database

I have a database table with customers:

Table:  
[Customer]

Fields:  
[Id] Integer  
[ParentId] Integer

Customers have, among other things, a unique Id and a ParentId that point to the customer that created it. So, every customer can have sub customers, whom can have sub customers etc.

Etc:

Customer{ Id = 1, ParentId = 1 }  //Root customer  
Customer{ Id = 123, ParentId = 1}  
Customer{ Id = 456, ParentId = 1}  
Customer{ Id = 789, ParentId = 1}  
Customer{ Id = 321, ParentId = 123 }  
Customer{ Id = 654, ParentId = 123 }  
Customer{ Id = 987, ParentId = 789 }
Customer{ Id = 1010, ParentId = 987 }

I use EF and LINQ to query my data.

What I need, is a LINQ query to get all Id's flattened, based in an Id.

E.g.:
Id 1 will return all Id's.
Id 123 will return 321 and 654
Id 789 will return 987 and 1010

Upvotes: 3

Views: 936

Answers (2)

Filip Cordas
Filip Cordas

Reputation: 2561

Here something that works for SQL server 

 var items = db.Database.SqlQuery<Table>(@"with [CTE] as (
        select * from [Table] c where c.[ParentId] = @id
        union all
        select c.* from [CTE] p, [Table] c where c.[ParentId] = p.[Id]
        and c.[ParentId] <> c.[Id]
    )
    select * from [CTE]", new SqlParameter("@Id", 1));
            var data = items.ToList();

You need to do raw sql but it works

Upvotes: 0

Magnus
Magnus

Reputation: 46909

It is not possible to write recursive linq queries that are translatable by EF into SQL. So you have two options.

  1. Write a recursive Common Table Expression in SQL and map the result to your entities.
  2. Bring the whole table into memory and traverse it in code.

Upvotes: 1

Related Questions