3

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

Jacobdp
  • 33
  • 4
  • Why does it have to be linq? – BugFinder Nov 30 '16 at 11:59
  • @ThePerplexedOne - because he is looking for recursive children: 789 -> 987 -> 1010 – PartlyCloudy Nov 30 '16 at 12:06
  • recursive links are difficult to do with sql and since EF linq just get translated to SQL you will need to think how to do it [there](https://www.codeproject.com/articles/818694/sql-queries-to-manage-hierarchical-or-parent-child). A stored procedure or a view might be the best thing to do. Or you can just pull all in memory and do it there. – Filip Cordas Nov 30 '16 at 12:13
  • Having root with self-referencing ParentId instead of nullable is a pain in EF if you ask me. As for the question: whats your tradeoff between loading extra entities and executing multiple queries? Maybe have a look at [this answer I recently wrote](http://stackoverflow.com/a/40874665/5265292) to another problem. Basically, get the hierarchy and then traverse it for flattening. – grek40 Nov 30 '16 at 12:15
  • Instead of self-referencing, use a `hierarchyid` column that's available in all supported SQL Server versions. This column can be indexed making children retrieval very fast. – Panagiotis Kanavos Nov 30 '16 at 13:26
  • I reopened the question because [EntityFramework.HierarchyId](https://www.nuget.org/packages/EntityFrameworkWithHierarchyId) is a fork of EF6, not an extension. – Panagiotis Kanavos Nov 30 '16 at 13:50
  • Is it possible to bring the IDs into memory? – Enigmativity Dec 01 '16 at 10:01

2 Answers2

1

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.
Magnus
  • 45,362
  • 8
  • 80
  • 118
  • [Well there is a way to do it in SQL](http://stackoverflow.com/questions/207309/sql-query-for-parent-child-relationship) – Filip Cordas Nov 30 '16 at 12:23
  • @FilipCordas not the preferred way for 4 years. `hierarchyid` is a better option, as it's indexable and a lot easier to use. It's available in all supported SQL Server versions – Panagiotis Kanavos Nov 30 '16 at 13:25
  • @FilipCordas Yes, a recursive Common Table Expression just as I wrote. – Magnus Nov 30 '16 at 15:57
0

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

Filip Cordas
  • 2,531
  • 1
  • 12
  • 23
  • There's no need to do this. You can use `hierarchyid` and avoid self-referencing. `hierarchyid` is supported in EF6 through a NuGet package – Panagiotis Kanavos Nov 30 '16 at 13:24
  • Sweet did not know this. Got any links with examples? – Filip Cordas Nov 30 '16 at 13:28
  • Check the duplicate question for starters, google `T-SQL hierarchyid`. The [MSDN tutorial](https://msdn.microsoft.com/en-us/library/bb677213.aspx) is *very* detailed. Check eg the [Querying](https://msdn.microsoft.com/en-us/library/bb677191.aspx) page for query examples, [Populating](https://msdn.microsoft.com/en-us/library/bb677174.aspx) to see how to insert top level and children entries – Panagiotis Kanavos Nov 30 '16 at 13:36