EF Core: How to translate "With table AS"

Question

I'am trying with EF Core to get nested categories. Problem is that it's so slow.

Look at my code:

    public async Task<List<Category>> GetNestedCategoryAsync(Category category)
    {
        if (category is null)
            return new List<Category>();

        IQueryable<Category> query = db.Categories.Include(x => x.Childs).Include(x => x.Products).Where(x => category.Childs.Contains(x)).AsNoTracking();

        List<Category> nestedCategories = await query.ToListAsync();

        foreach (Category nestedCategory in nestedCategories.ToArray())
            nestedCategories.AddRange(await GetNestedCategoryAsync(nestedCategory));

        return nestedCategories;
    }

Actually I don't know how to translate this SQL into EF.. is it even possible? Its thousand times faster

With Categories_CTE As
(
Select *
From Categories
Where Id = 8692

Union All
Select t.*
From Categories t
Inner Join Categories_CTE c On c.Id = t.ParentId
)

Select c.*
From Categories_CTE c;

Thanks for any tips

Possible duplicate of [Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax](https://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax) — Sami Kuhmonen, Mar 06 '18 at 17:20

score 2 · Answer 1 · answered Mar 06 '18 at 17:18

2

Entity Framework will never generate a CTE, that's a step too far for it. However, you can use your SQL through EF Core like this:

var categories = db.Categories.FromSql("WITH Categories_CTE AS .......");

answered Mar 06 '18 at 17:18

DavidG

113,891
12
217
223

Ok thanks for info, I found this way also. Is there any tutorial how to translate Include like INNER JOIN with query I posted? Is enough just inner join Products ON x... ? – user1085907 Mar 06 '18 at 17:39
You should be able to do `db.Categories.FromSql("...").Include(c => c.Childs)` – DavidG Mar 06 '18 at 17:48
1

I am getting error "The Include operation is not supported when calling a stored procedure." so I have to select it and somehow navigate – user1085907 Mar 06 '18 at 17:51

EF Core: How to translate "With table AS"

1 Answers1