Expression With and Union All to EF Core

Question

I want to convert this SQL query to EF Core code. I don't want to use LINQ, any possibility?

with cat (id, id_parent, name) as
(
    select id, id_parent, name
    from categories
    where categories.id = 9

    union all

    select e.id, e.id_parent, e.name
    from dbo.categories e
    inner join brands b on b.id_parent = e.id
); 
select * from cat;

Answer 1

If I get your question correctly this would work for you:

 var result = await _dbContext.Categories
                .Where(x => x.Id == 9)
                .Select(x => new { Id = x.Id, Parent = x.IdParent, Name = x.Name})
                .Concat(
                    _dbContext.Categories.Join(_dbContext.Brands, cat=> cat.Id, brand=> brand.IdParent, (cat,brand)=>cat)
                    .Select(x => new { Id = x.Id, Parent = x.IdParent, Name = x.Name }))
                .ToListAsync();