C#/Linq How to get all records by ParentId and then by FolderId recursively

Question

I have the following setup for a table:

I need to obtain all the folders tied to a certain ParentId (only the direct children) I also need to be able to pass in a FolderId and then obtain the full path of records to get to that FolderId starting at the top level (null parent id)

I've tried .SelectMany(), but I have this error:

The type arguments for method 'Enumerable.SelectMany<TSource, TResult>(IEnumerable, Func<TSource, IEnumerable>)' cannot be inferred from the usage. Try specifying the type arguments explicitly

        public async Task<IList<FolderDTO>> GetFullPathByFolderId(int? parentId, bool onlyDirectChildren)
        {
            var allFolders = await GetAllFolders(false).ConfigureAwait(false);

            return allFolders.Where(x => x.ParentId == parentId)
            .Union(allFolders.Where(x => x.ParentId == parentId)
                        .SelectMany(y => GetFullPathByFolderId(y.FolderId)));

        }

So if ParentId of null is passed in I need to return 4 records: Folder names, in order: Private, Public, Shared, Private Deleted.

If FolderId is passed I need to return 2 records: Folder Names, in order: Untitled Directory, Public.

Is there a way to combine this into one or two methods?

CREATE TABLE [dbo].[Folders](
    [FolderId] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [FolderName] [varchar](150) NOT NULL,
    [FolderTypeId] [int] NOT NULL,
    [IsHidden] [bit] NOT NULL,
    [DateModified] [datetime] NOT NULL,
    [ModifiedBy] [int] NOT NULL,
    [DateSoftDeleted] [datetime] NULL,
    [SoftDeletedBy] [int] NULL,
 CONSTRAINT [PK_Folders] PRIMARY KEY CLUSTERED 
(
    [FolderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

UPDATED CODE:

Here is some code.

I would want to pass in FolderId 1 into the LINQ/C# Call and return the names: Private and Private1

    namespace App
    {
        /// <summary>
        /// FolderTypeDTO
        /// </summary>
        public class FolderTypeDTO
        {
            /// <summary>
            /// Gets or sets the folder type identifier.
            /// </summary>
            /// <value>
            /// The folder type identifier.
            /// </value>
            public int FolderTypeId { get; set; }
    
            /// <summary>
            /// Gets or sets the name of the folder type.
            /// </summary>
            /// <value>
            /// The name of the folder type.
            /// </value>
            public string FolderTypeName { get; set; }
        }
    }
    
    namespace App
    {
        using System;
    
        /// <summary>
        /// Folder DTO
        /// </summary>
        public class FolderDTO
        {
            /// <summary>
            /// Gets or sets the folder identifier.
            /// </summary>
            /// <value>
            /// The folder identifier.
            /// </value>
            public int FolderId { get; set; }
    
            /// <summary>
            /// Gets or sets the parent identifier.
            /// </summary>
            /// <value>
            /// The parent identifier.
            /// </value>
            public int? ParentId { get; set; }
    
            /// <summary>
            /// Gets or sets a value indicating whether this instance has sub folders.
            /// </summary>
            /// <value>
            ///   <c>true</c> if this instance has sub folders; otherwise, <c>false</c>.
            /// </value>
            public bool HasSubFolders { get; set; }
    
            /// <summary>
            /// Gets or sets the name of the folder parent.
            /// </summary>
            /// <value>
            /// The name of the folder parent.
            /// </value>
            public string FolderParentName { get; set; }
    
            /// <summary>
            /// Gets or sets the name of the folder.
            /// </summary>
            /// <value>
            /// The name of the folder.
            /// </value>
            public string FolderName { get; set; }
    
            /// <summary>
            /// Gets or sets the folder type identifier.
            /// </summary>
            /// <value>
            /// The folder type identifier.
            /// </value>
            public int FolderTypeId { get; set; }
    
            /// <summary>
            /// Gets or sets the name of the folder type.
            /// </summary>
            /// <value>
            /// The name of the folder type.
            /// </value>
            public string FolderTypeName { get; set; }
    
            /// <summary>
            /// Gets or sets a value indicating whether this instance is hidden.
            /// </summary>
            /// <value>
            ///   <c>true</c> if this instance is hidden; otherwise, <c>false</c>.
            /// </value>
            public bool IsHidden { get; set; }
    
            /// <summary>
            /// Gets or sets the level.
            /// </summary>
            /// <value>
            /// The level.
            /// </value>
            public int Level { get; set; }
    
            /// <summary>
            /// Gets or sets the date modified.
            /// </summary>
            /// <value>
            /// The date modified.
            /// </value>
            public DateTime DateModified { get; set; }
    
            /// <summary>
            /// Gets or sets the modified by.
            /// </summary>
            /// <value>
            /// The modified by.
            /// </value>
            public int ModifiedBy { get; set; }
    
            /// <summary>
            /// Gets or sets the name of the modified by.
            /// </summary>
            /// <value>
            /// The name of the modified by.
            /// </value>
            public string ModifiedByName { get; set; }
    
            /// <summary>
            /// Gets or sets the date soft deleted.
            /// </summary>
            /// <value>
            /// The date soft deleted.
            /// </value>
            public DateTime? DateSoftDeleted { get; set; }
    
            /// <summary>
            /// Gets or sets the soft deleted by.
            /// </summary>
            /// <value>
            /// The soft deleted by.
            /// </value>
            public int? SoftDeletedBy { get; set; }
    
            /// <summary>
            /// Gets or sets the name of the soft deleted by.
            /// </summary>
            /// <value>
            /// The name of the soft deleted by.
            /// </value>
            public string SoftDeletedByName { get; set; }
        }
    }

       var folderTypeList = new List<FolderTypeDTO>
        {
            new FolderTypeDTO
            {
                FolderTypeId = 1,
                FolderTypeName = "Private"
            },
            new FolderTypeDTO
            {
                FolderTypeId = 2,
                FolderTypeName = "Public"
            },
            new FolderTypeDTO
            {
                FolderTypeId = 3,
                FolderTypeName = "Shared"
            },
        };
    
        var folderList = new List<FolderDTO>
        {
            new FolderDTO
            {
                FolderId = 1,
                ParentId = null,
                FolderName = "Private",
                FolderTypeId = 1,
                IsHidden = false,
                DateModified = DateTime.UtcNow,
                ModifiedBy = 107 // my user id in our app
            },
            new FolderDTO
            {
                FolderId = 2,
                ParentId = null,
                FolderName = "Public",
                FolderTypeId = 2,
                IsHidden = false,
                DateModified = DateTime.UtcNow,
                ModifiedBy = 107 // my user id in our app
            },
            new FolderDTO
            {
                FolderId = 3,
                ParentId = 1,
                FolderName = "Private 1",
                FolderTypeId = 1,
                IsHidden = false,
                DateModified = DateTime.UtcNow,
                ModifiedBy = 107 // my user id in our app
            }
        };

Answer 1

Thank you for the code. It's now easy to get a result.

Start with a lookup that will help generate a recursive set of FolderDTO objects by parentID.

ILookup<int?, FolderDTO> lookup = folderList.ToLookup(x => x.ParentId);

Now we can write a recursive method to return the full set of FolderDTO objects with the provided parentID.

IEnumerable<FolderDTO> GetHierarchically(int? parentId) =>
    from x in lookup[parentId]
    from y in GetHierarchically(x.FolderId).StartWith(x)
    select y;

Now it's a fairly simple case of doing a Concat and a Join to get the results you need.

IEnumerable<string> GetFolderNamesHierarchicallyByIdAndType(int parentId, string folderTypeName) =>
    from x in
        Enumerable
            .Concat(
                folderList.Where(f => f.FolderId == parentId),
                GetHierarchically(parentId))
    join y in folderTypeList on x.FolderTypeId equals y.FolderTypeId
    where y.FolderTypeName == folderTypeName
    select x.FolderName;

This can be called like this:

string[] names = GetFolderNamesHierarchicallyByIdAndType(1, "Private").ToArray();

The results I get from your sample data are:

Private 
Private 1