c# - TSQL - 从具有多个连接路径的表中选择

Question

这个标题不是很好，所以考虑以下内容。我有五个表:

User {
    Id,
    ProfileId        // -> Profiles.Id
}

Profile {
    Id
}

ProfilePermissionSets {
    ProfileId        // -> Profiles.Id
    PermissionSetId  // -> PermissionSets.Id
}

UserPermissionSets {
    UserId           // -> Users.Id
    PermissionSetId  // -> PermissionSets.Id
}

PermissionSets {
    Id
}

Permissions {
    Id,
    PermissionSetId  // -> PermissionSets.Id
}

我想获得直接链接到用户或通过配置文件间接链接的用户的所有权限。到目前为止，我提出的不太完善的 SQL 是这样的:

SELECT  [Pe].[Controller],
        [Pe].[Action]
FROM    [PermissionSets] AS [PS]
        JOIN [UserPermissionSets] AS [UPS]
            ON ([UPS].[PermissionSetId] = [PS].[Id])
        JOIN [Users] AS [U]
            ON ([U].[Id] = [UPS].[UserId])
        JOIN [Profiles] AS [P]
            ON ([P].[Id] = [U].[ProfileId])
        JOIN [ProfilePermissionSets] AS [PPS]
            ON ([PPS].[ProfileId] = [P].[Id])
        JOIN [Permissions] AS [Pe]
            ON ([Pe].[PermissionSetId] = [PS].[Id])
WHERE   [U].[Id] = 4;

它返回正确的行数，但它一遍又一遍地重复 Controller 或操作，所以这是错误的。我希望有人可以帮助我更正它以显示用户的所有不同权限集。理想情况下，我还想更改它，以便从用户开始发现它，因为这是我在需要执行此操作的方法中可以访问的内容(该对象是一个名为 User 的 Entity Framework 类，并且将是使用 LINQ 浏览)。

已更新 因为我忘记了我真的想要权限而不是权限集。

Answer 1

试试这个 SQL

SELECT  [Pe].[Controller],
        [Pe].[Action]
FROM    [Users] AS [U]
    LEFT OUTER JOIN [UserPermissionSets] AS [UPS]
            ON ([UPS].[UserId] = [U].[Id])
    LEFT OUTER JOIN [ProfilePermissionSets] AS [PPS]
            ON ([PPS].[ProfileId] = [U].[ProfileId])
    LEFT OUTER JOIN [Permissions] AS [Pe]
            ON ([Pe].[PermissionSetId] = [UPS].[PermissionSetId])
            OR ([Pe].[PermissionSetId] = [PPS].[PermissionSetId])
WHERE   [U].[Id] = 4;