How to convert LINQ nested Selectmany to SQL Regular Statements

Question

I know it's ain't gonna be easy but I'm stuck on this and can't move on. I have this 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
                        }
                });

And now the task is to move this into a SP and I don't want to take the sql generated by the EF, it's clumsy and machine-generated.

I ended up having this:

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)

Now my problem is that Progress column calculation which includes a few repeating SelectMany statements and I don't know how to do with this. Any help is really appreciated, guys.

There's a EncodingJobs table having a FK ResourceItemId (0 to many) to the resourceItem table, and there's another table EncodingTask with a FK EncodingJobId (the same 0 to many).

This is what EF generates:

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]

Answer 1

Well this should be something like that.

Cast the count to decimal if Progress is an integer, to avoid integer division.If not, you can avoid the cast

SELECT
    ri.Id
    ,ri.CreationDate
    ,ri.FolderId
    ,ri.Name
    ,ri.ResourceType
    ,ri.Url
    ,ri.Size
    ,ri.MediaAssetUuid
    ,ri.Blob
    ,ri.Container
    ,ri.GroupId
    coalesce(sum(et.Progress) / cast(count(*) as decimal(18,2)), 0) 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
LEFT JOIN EncodingJob ej on ej.ResourceItemId= ri.Id
LEFT JOIN EncodingTask et on et.JobId = ej.Id
WHERE
    ri.IsDeleted = 0
group by
ri.Id
    ,ri.CreationDate
    ,ri.FolderId
    ,ri.Name
    ,ri.ResourceType
    ,ri.Url
    ,ri.Size
    ,ri.MediaAssetUuid
    ,ri.Blob
    ,ri.Container
    ,ri.GroupId
    ,ri.Uuid
    ,u.Id AS UserId
    ,u.UserName
    ,u.FirstName
    ,u.LastName

Answer 2

you just need to think what you are looking for. In this case its the total of the progress / count, grouped per resourceItem.

The following should be about right, but the IDs might need correcting! This uses a common table expression (SQL Server) but could easily be rewritten to a subquery

;WITH prog AS
(
    SELECT 
        ej.ResourceItemId,
        SUM(et.Progress) / COUNT(*) AS totalProg
    FROM EncodingJobs ej
    JOIN EncodingTasks et ON ej.Id = et.EncodingJobId
    GROUP BY
        ej.ResourceItemId
)
SELECT
    ri.Id
    ,ri.CreationDate
    ,ri.FolderId
    ,ri.Name
    ,ri.ResourceType
    ,ri.Url
    ,ri.Size
    ,ri.MediaAssetUuid
    ,ri.Blob
    ,ri.Container
    ,ri.GroupId
    ,ISNULL(prog.totalProg, 0) 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
LEFT JOIN prog ON ri.Id = prog.ResourceItemId
WHERE
    ri.IsDeleted = CAST(0 as BIT)

Answer 3

You can attach SQL Server Profiler to my database and run the application. SQL Server Profiler will capture the SQL that is being run on the database. You can then use that SQL as a starting point for your stored procedure.

SQL Server Profiler Tutorial