Common Table Expression (CTE)

[arturwv]

There is need to make recursive query to db. by LINQ
Defining recursive CTE

	WITH CTELocationsTree
	AS
	(
	SELECT [Id],[Name],[Code],[ParentId],[Discriminator],[Address],[Capacity],[something], Convert(NVARCHAR(MAX),[Id]) as HierarchyPath
	FROM [dbo].[Locations] WHERE Id = 1 

	UNION ALL

	SELECT loc.[Id],loc.[Name],loc.[Code],loc.[ParentId],loc.[Discriminator],loc.[Address],loc.[Capacity],loc.[something] , Convert(NVARCHAR(MAX),HierarchyPath +'/'+ Convert(NVARCHAR(20),loc.Id))  as HierarchyPath
	FROM [dbo].[Locations] loc
	INNER JOIN CTELocationsTree cloc  ON loc.ParentId = cloc.Id
	)
	SELECT * FROM CTELocationsTree cte 

Something like that (LINQ to DB)

https://linq2db.github.io/articles/sql/CTE.html?q=Common%20Table%20Expression%20(CTE)

That is create query by LINQ which will produce recursive CTE SqlQuery and map data to class.

Class example

  public abstract  class Location 
    {

        public int Id { get; set; }
        public string Name { get; set; }
        public string Code { get; set; }

        public int? ParentId
        {
            get; set;
        }

        [ForeignKey("ParentId")]
        public virtual Location? Parent { get; set; }

        public virtual IEnumerable<Location>? Children { get; set; }
        public string? HierarchyPath { get; set ; }
    }

    public class Warehouse : Location
    {
        public int Capacity { get; set; }
        public int Something{ get; set; }
    }

[roji]

Duplicate of #26486

[roji]

(or possibly #29918)

[arturwv]

so you won't, nice :/

[roji]

@arturwv you just opened a duplicate issue - me closing it doesn't mean it won't get implemented (it's just not helpful to have two issues tracking the same thing).

In fact, I do think this is important and hope to be able to do it for EF Core 9.0.

[arturwv]

thanks.