如何将LINQ嵌套Selectmany转换为SQL正则语句

我知道这不容易，但我被困在这里，无法继续。我有这个linq

    var resourceItems = queryable
                .Select(ri => new ResourceItemDto
                {
                    Id = ri.Id,
                    CreationDate = ri.CreationDate,
                    ParentId = ri.FolderId,
                    Name = ri.Name,
                    Type = ri.ResourceType,
                    Url = ri.Url,
                    Size = ri.Size,
                    MediaAssetUuid = ri.MediaAssetUuid,
                    Blob = ri.Blob,
                    Container = ri.Container,
                    GroupId = ri.GroupId,
                    Status = (ResourceItemStatus) ri.Status,
                    Progress =
                        ri.EncodingJobs.SelectMany(j => j.EncodingTasks).Any()
                            ? (ri.EncodingJobs.SelectMany(j => j.EncodingTasks).Sum(t => (decimal?) t.Progress)/
                               ri.EncodingJobs.SelectMany(j => j.EncodingTasks).Count() ?? 0M)
                            : 0M,
                    Uuid = ri.Uuid,
                    CreatedBy =
                        new UserDto
                        {
                            Id = ri.User.Id,
                            UserName = ri.User.UserName,
                            FirstName = ri.User.FirstName,
                            LastName = ri.User.LastName
                        }
                });

现在的任务是将其移动到SP中，我不想使用EF生成的sql，它是笨拙的机器生成的。

我最终得到了这个:

SELECT
    ri.Id
    ,ri.CreationDate
    ,ri.FolderId
    ,ri.Name
    ,ri.ResourceType
    ,ri.Url
    ,ri.Size
    ,ri.MediaAssetUuid
    ,ri.Blob
    ,ri.Container
    ,ri.GroupId
    --, (sql_expression) AS Progress
    ,ri.Uuid
    ,u.Id AS UserId
    ,u.UserName
    ,u.FirstName
    ,u.LastName
FROM ResourceItem ri
INNER JOIN ResourceItemsTree rit ON ri.FolderId = rit.Id
INNER JOIN [User] u ON u.Id = ri.CreatedBy
WHERE
    ri.IsDeleted = CAST(0 as BIT)

现在我的问题是进度列计算，其中包括一些重复的SelectMany语句，我不知道如何处理这个。任何帮助都是非常感谢的，伙计们。

有一个EncodingJobs表具有一个FK ResourceItemId(0到多)到resourceItem表，并且有另一个表EncodingTask具有一个FK EncodingJobId(相同的0到多)。

这是EF生成的:

SELECT 
[Project4].[Id] AS [Id], 
[Project4].[CreationDate] AS [CreationDate], 
[Project4].[FolderId] AS [FolderId], 
[Project4].[Name] AS [Name], 
[Project4].[ResourceType] AS [ResourceType], 
[Project4].[Url] AS [Url], 
[Project4].[Size] AS [Size], 
[Project4].[MediaAssetUuid] AS [MediaAssetUuid], 
[Project4].[Blob] AS [Blob], 
[Project4].[Container] AS [Container], 
[Project4].[GroupId] AS [GroupId], 
[Project4].[Status] AS [Status], 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM  [dbo].[EncodingJob] AS [Extent12]
    INNER JOIN [dbo].[EncodingTask] AS [Extent13] ON [Extent12].[Id] = [Extent13].[JobId]
    WHERE [Project4].[Id] = [Extent12].[ResourceItemId]
)) THEN CASE WHEN ([Project4].[C1] /  CAST( [Project4].[C2] AS decimal(19,0)) IS NULL) THEN cast(0 as decimal(18)) ELSE [Project4].[C3] /  CAST( [Project4].[C4] AS decimal(19,0)) END ELSE cast(0 as decimal(18)) END AS [C1], 
[Project4].[Uuid] AS [Uuid], 
[Project4].[CreatedBy] AS [CreatedBy], 
[Project4].[UserName] AS [UserName], 
[Project4].[FirstName] AS [FirstName], 
[Project4].[LastName] AS [LastName]
FROM ( SELECT 
    [Project3].[Id] AS [Id], 
    [Project3].[FolderId] AS [FolderId], 
    [Project3].[Name] AS [Name], 
    [Project3].[ResourceType] AS [ResourceType], 
    [Project3].[Url] AS [Url], 
    [Project3].[Size] AS [Size], 
    [Project3].[MediaAssetUuid] AS [MediaAssetUuid], 
    [Project3].[Status] AS [Status], 
    [Project3].[CreationDate] AS [CreationDate], 
    [Project3].[GroupId] AS [GroupId], 
    [Project3].[Container] AS [Container], 
    [Project3].[Blob] AS [Blob], 
    [Project3].[Uuid] AS [Uuid], 
    [Project3].[CreatedBy] AS [CreatedBy], 
    [Project3].[UserName] AS [UserName], 
    [Project3].[FirstName] AS [FirstName], 
    [Project3].[LastName] AS [LastName], 
    [Project3].[C1] AS [C1], 
    [Project3].[C2] AS [C2], 
    [Project3].[C3] AS [C3], 
    (SELECT 
        COUNT(1) AS [A1]
        FROM  [dbo].[EncodingJob] AS [Extent10]
        INNER JOIN [dbo].[EncodingTask] AS [Extent11] ON [Extent10].[Id] = [Extent11].[JobId]
        WHERE [Project3].[Id] = [Extent10].[ResourceItemId]) AS [C4]
    FROM ( SELECT 
        [Project2].[Id] AS [Id], 
        [Project2].[FolderId] AS [FolderId], 
        [Project2].[Name] AS [Name], 
        [Project2].[ResourceType] AS [ResourceType], 
        [Project2].[Url] AS [Url], 
        [Project2].[Size] AS [Size], 
        [Project2].[MediaAssetUuid] AS [MediaAssetUuid], 
        [Project2].[Status] AS [Status], 
        [Project2].[CreationDate] AS [CreationDate], 
        [Project2].[GroupId] AS [GroupId], 
        [Project2].[Container] AS [Container], 
        [Project2].[Blob] AS [Blob], 
        [Project2].[Uuid] AS [Uuid], 
        [Project2].[CreatedBy] AS [CreatedBy], 
        [Project2].[UserName] AS [UserName], 
        [Project2].[FirstName] AS [FirstName], 
        [Project2].[LastName] AS [LastName], 
        [Project2].[C1] AS [C1], 
        [Project2].[C2] AS [C2], 
        (SELECT 
            SUM([Extent9].[Progress]) AS [A1]
            FROM  [dbo].[EncodingJob] AS [Extent8]
            INNER JOIN [dbo].[EncodingTask] AS [Extent9] ON [Extent8].[Id] = [Extent9].[JobId]
            WHERE [Project2].[Id] = [Extent8].[ResourceItemId]) AS [C3]
        FROM ( SELECT 
            [Project1].[Id] AS [Id], 
            [Project1].[FolderId] AS [FolderId], 
            [Project1].[Name] AS [Name], 
            [Project1].[ResourceType] AS [ResourceType], 
            [Project1].[Url] AS [Url], 
            [Project1].[Size] AS [Size], 
            [Project1].[MediaAssetUuid] AS [MediaAssetUuid], 
            [Project1].[Status] AS [Status], 
            [Project1].[CreationDate] AS [CreationDate], 
            [Project1].[GroupId] AS [GroupId], 
            [Project1].[Container] AS [Container], 
            [Project1].[Blob] AS [Blob], 
            [Project1].[Uuid] AS [Uuid], 
            [Project1].[CreatedBy] AS [CreatedBy], 
            [Project1].[UserName] AS [UserName], 
            [Project1].[FirstName] AS [FirstName], 
            [Project1].[LastName] AS [LastName], 
            [Project1].[C1] AS [C1], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM  [dbo].[EncodingJob] AS [Extent6]
                INNER JOIN [dbo].[EncodingTask] AS [Extent7] ON [Extent6].[Id] = [Extent7].[JobId]
                WHERE [Project1].[Id] = [Extent6].[ResourceItemId]) AS [C2]
            FROM ( SELECT 
                [Extent1].[Id] AS [Id], 
                [Extent1].[FolderId] AS [FolderId], 
                [Extent1].[Name] AS [Name], 
                [Extent1].[ResourceType] AS [ResourceType], 
                [Extent1].[Url] AS [Url], 
                [Extent1].[Size] AS [Size], 
                [Extent1].[MediaAssetUuid] AS [MediaAssetUuid], 
                [Extent1].[Status] AS [Status], 
                [Extent1].[CreationDate] AS [CreationDate], 
                [Extent1].[GroupId] AS [GroupId], 
                [Extent1].[Container] AS [Container], 
                [Extent1].[Blob] AS [Blob], 
                [Extent1].[Uuid] AS [Uuid], 
                [Extent1].[CreatedBy] AS [CreatedBy], 
                [Extent2].[UserName] AS [UserName], 
                [Extent3].[FirstName] AS [FirstName], 
                [Extent3].[LastName] AS [LastName], 
                (SELECT 
                    SUM([Extent5].[Progress]) AS [A1]
                    FROM  [dbo].[EncodingJob] AS [Extent4]
                    INNER JOIN [dbo].[EncodingTask] AS [Extent5] ON [Extent4].[Id] = [Extent5].[JobId]
                    WHERE [Extent1].[Id] = [Extent4].[ResourceItemId]) AS [C1]
                FROM   [dbo].[ResourceItem] AS [Extent1]
                INNER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[CreatedBy] = [Extent2].[Id]
                LEFT OUTER JOIN [dbo].[User] AS [Extent3] ON [Extent1].[CreatedBy] = [Extent3].[Id]
                WHERE ([Extent1].[IsDeleted] <> cast(1 as bit)) AND ([Extent1].[FolderId] = @p__linq__0)
            )  AS [Project1]
        )  AS [Project2]
    )  AS [Project3]
)  AS [Project4]