I have the following table in Sql Server that stores the permissions the Foreign Key UserId
has to the Foreign Keyed BlogId
. What I would like to do is write a query with Dapper that takes each column after BlogId
and returns it as a KeyValuePair<TKey, TValue>
.
CREATE TABLE [dbo].[UserPermission] (
[UserPermissionId] INT IDENTITY (1, 1) NOT NULL,
[BlogId] INT NOT NULL,
[UserId] INT NOT NULL,
[CanCreateNewPosts] BIT NOT NULL,
[CanEditExistingPosts] BIT NOT NULL,
[CanDeleteExistingPosts] BIT NOT NULL,
[CanPublishDraftPosts] BIT NOT NULL,
CONSTRAINT [PK_UserPermission] PRIMARY KEY CLUSTERED ([UserPermissionId] ASC),
CONSTRAINT [FK_UserPermission_Blog] FOREIGN KEY ([BlogId]) REFERENCES [dbo].[Blog] ([BlogId]),
CONSTRAINT [FK_UserPermission_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId])
);
I would like to query via Dapper like this:
using (var connection = new SqlConnection(this.connectionString))
{
string sql = "SELECT * FROM [Permission] WHERE UserId = @UserId";
await connection.OpenAsync();
IEnumerable<KeyValuePair<string, bool>> results = await connection.QueryAsync(
sql,
new { UserId = this.userId });
}
var p1 = results.First();
var p2 = results.Skip(1).First();
In the above example, I would like p1
to result in a KeyValuePair
with the Key being CanCreateNewPosts
and the Value being the column value, either true or false. Same applies with p2
, where the Key would be CanEditExistingPosts
, with it's corresponding value.
The underlying need for this is to simplify transforming the record into a list of Claims in Identity, one claim per column.
I looked at splitOn:
, to try and split after the UserId
column but that doesn't seem like it's what I want. It would require n-generic arguments for each column I split. Ideally I'd like to add columns to this table in the future and my security/data/servicing layer just handles turning it into a Claim - letting me just focus on the Controller Action that needs to check for the claim. Having the query and Dapper return map the column-name/values into a collection of KeyValuePairs
would facilitate that need for me.
You can use the SQL UNPIVOT
operation to transform columns into column values. Here's an example for your particular case:
SELECT u.BlogId, u.permission
FROM UserPermissions up
UNPIVOT
(
permission for perms in (
CanCreateNewPosts,
CanEditExistingPosts,
CanDeleteExistingPosts,
CanPublishDraftPosts
)
) u;