Loop recursive self join table

Question

I have the following SQL Server table structure, it's a logical tree folder structure.

I'm using Linq to SQL with SQL Server.

What I want to do is to loop and create on memory the folder structure, the hierarchical folder from Root, the main parent to all childs.

I'm trying to do it with some foreach loops but I still don't get it. I think I must use some kind of recursive way.

Some query I tried:

var flds = from folders in repo.Folders
                                   join folders2 in repo.Folders on folders.Id equals folders2.idParent
                                   select folders;

My goal is to iterate that folder logical structure.

Answer 1

If you want to get folder as hierarchy given in your table, you can create it like this :

    var tableData = repo.Folders.ToList();
    var directories = tableData.Select(f => GetPath(f, table));

And as you asked, the recursive method for creating a folder hierarchy is :

    private static string GetPath(Folder f)
    {
        if (f.IdParent == 0)
        {
            return f.Name;
        }
        else
        {
            var parent = tableData.Find(d => d.Id == f.IdParent);
            return GetPath(parent) + "|" + f.Name;
        }
    }

You will get output like this :

It will give you a complete folder hirarchy, I don't know how you want to use it. So you can do some modification as per your need. Hope it helps!

Here I am adding the code to iterate and create the folder structure :

        foreach (var dir in directories)
        {
            var folders = dir.Split('|');
            var path = string.Empty;
            foreach (var folder in folders)
            {
                path = path + "\\" + folder;  // modify the path like '\\' if it is not valid I have not tested
                CreateFolder(path); // implement this method for actual creation of folders
            }
        }

And your method for creating folders :

private static void CreateFolder(string directory)
    {
        var path = "C:" + directory; // add whenever you want to create structure
        if (!Directory.Exists(path))
        {
            Directory.CreateDirectory(path);
        }
    }

Answer 2

You could try this one:

// Get a list of the folders.
// We do this, in order to avoid the performance hit, that Panagiotis 
// pointed out correctly in his comment.
var folders = repo.Folders.ToList();

// Get the root folder.
var rootFolder = folders.Where(x=>x.idParent==null).SingleOrDefault();

// Group the folders by their parentId.
var groupedFolders = from folder in folders
                     where folder.idParent!=null
                     orderby folder.idParent ascending
                     group folder by folder.idParent into grp
                     select new
                     {
                         ParentFolderId = grp.Key,
                         Folders = grp.Select(x=>x)
                     };

// Print the name of the root folder.
Console.WriteLine("Root Folder", rootFolder.Name);

// Iterate through the groups of folders. 
foreach(var grp in groupedFolders)
{
    // Get the name of the parent folder for the current group.
    string parentFolderName = folders.Where(x=>x.Id==grp.ParentFolderId)
                                     .Single(x=>x.Name);


    // Print the name of the parent folder.
    Console.WriteLine("Parent Folder", parentFolderName);

    // Iterate through the folders of the current group.
    foreach(var folder in grp.Folders)
    {
        Console.WriteLine(folder.Name);
    }
}