CTE (Common Table Expression) in EFCore

Question

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

Answer 1

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.