LINQ to SQL recursive problem

I want to convert the following query (which is working correctly) from query syntax to LINQ syntax, but I can't seem to get it right:

SELECT
    [t1].[ID], [t1].[ParentID], [t1].[FolderName],
    [t1].[Purpose], [t1].[IsSystem],t1].IsHidden],
    [t1].[ChangeSrc], [t1].[SyncGUID], [t1].[CreationDBTimeStamp]
FROM [dbo].[FileStorageFolders] AS [t0]
INNER JOIN [dbo].[FileStorageFolders] AS [t1] ON ([t0].[ID]) = [t1][ParentID]
WHERE ([t1].[Purpose] = @p0)
    AND ([t1].[FolderName] = @p1)
    AND ([t0].[ParentID] IS NULL)
    AND ([t0].[Purpose] = @p2)

I use this LINQ syntax but the result always null value:

 private static FileStorageFolder GetCapsuleContentFolder(FileStorageDataContext db)
    {    IQueryable<FileStorageFolder> source = (from dbfolder in db.FileStorageFolders
            join dbsubfolder in db.FileStorageFolders on
                new { ParentID = dbfolder.ID } equals
                new { ParentID = Convert.ToInt32(dbsubfolder.ParentID) }
            where
                dbfolder.ParentID == null &&
                dbfolder.Purpose == 'REPORT' &&
                dbsubfolder.Purpose == 'LayoutFolder' &&
                dbsubfolder.FolderName == 'LayoutReport'
            select new
                    {
                        dbsubfolde开发者_运维百科r.ID,
                        ParentID = (System.Int32?)dbsubfolder.ParentID,
                        dbsubfolder.FolderName,
                        dbsubfolder.Purpose,
                        dbsubfolder.IsSystem,
                        pqrentID2 = dbfolder.ParentID,
                        Purpose2 = dbfolder.Purpose
                    }) as IQueryable<FileStorageFolder>;
return source.Single();
}

---

Do you have a collection of child foleders on parent? You should if your relation is mapped. In such case you can try:

var query = from dbFolder in db.FileStorageFolders
            from subFolder in dbFolder.SubFolders // Here I use that mapped relations
            where ...
            select new { ... };

The problem with your query is that you are creating anonymous type and converting it to your type - it is not possible. Also it is not full hierarchical query (your SQL is not as well). It will select only one level of sub folders.