8

I have a table in my DB that stores the hierarchy of locations. It has 3 columns (Id, Name, ParentId). I need to load some row base on a condition and all their parents up to the root. If it was in ADO I would use the following statement.

with Temp
as
(
    select * from Locations where Name like '%filter%'

    union all

    select Locations.* 
    from Temp join Locations on Temp.ParentId = Locations.Id
)
select * from Temp

I am using EFCore and with a few searches I have found How does Entity Framework work with recursive hierarchies? Include() seems not to work with it , How to do recursive load with Entity framework? and a bunch of others which they are all old.

All the solutions I found either hardcoded the depth of the hierarchy (using Include) or do the recursion in C#. All that set, my question is that what is the best solution to do so?

I can use FromSqlRaw (Something like the following code) however I don't like writing queries manually in C#.

    var locations = DataContext.Locations
    .FromSqlRaw("MyQuery").ToList();

I am using EFCore 3.1.7

Dale K
  • 25,246
  • 15
  • 42
  • 71
Hamid Mayeli
  • 805
  • 1
  • 14
  • 24
  • With Oracle you have depthfirst/breadth first - not sure if SQL Server has the same. So perhaps this will help: https://stackoverflow.com/questions/11636420/a-real-recursion-with-cte/11637387#11637387 – JGFMK Sep 07 '20 at 08:21
  • Thank you for your comment @JGFMK but this link is the answer for SQL, not the EFCore. I have already provided it in my question. – Hamid Mayeli Sep 07 '20 at 08:37
  • 1
    If you don't like embedding the raw query text you can always write a view, stored procedure or table-valued function to return the data, and access that. I have no direct experience with EF Core but I'd be surprised if it has any support for recursive queries. – Jeroen Mostert Sep 07 '20 at 08:51
  • Thanks @JeroenMostert, As I am using code first I should write raw queries to create the SP or View. So, it is not what I am looking for. – Hamid Mayeli Sep 07 '20 at 11:21

1 Answers1

6

CTEs are not supported in EF Core out-of-the-box, see issue #26486. However, library linq2db supports CTE and it can be connected to EF Core.

Jan Joneš
  • 777
  • 6
  • 13