public async Task <PagedList <ListUserChatsQueryResult> > ListUserChatsPaginated(Guid userId, int page, int pageSize)
{
string query = "select channels.id, channels.name, users_channels.isAdministrator "
+ "from users_channels "
+ "inner join channels on channels.id = users_channels.channelId "
+ "where users_channels.userId = @UserId "
+ "order by users_channels.createdOn desc "
+ "OFFSET @Offset ROWS "
+ "FETCH next @PageSize Rows ONLY\n"
+ "select COUNT(*) from users_channels "
+ "where users_channels.userId = @UserId";
int offset = (page - 1) * pageSize;
IEnumerable <ListUserChatsQueryResult> result;
using (SqlMapper.GridReader gridReader = await _connection.QueryMultipleAsync(query, new
{
PageSize = pageSize,
Offset = offset,
UserId = userId
}))
{
result = gridReader.Read <ListUserChatsQueryResult>();
int totalCount = await gridReader.ReadFirstAsync <int>();
return(new PagedList <ListUserChatsQueryResult>(page, pageSize, totalCount, result));
}
}
public async Task <PagedList <ListChannelsQueryResult> > ListChannelsPaginated(int page, int pageSize)
{
string query = "select channels.id, channels.name, channels.description, "
+ "users.id as 'administratorId', users.username as 'administratorUsername' "
+ "from channels "
+ "inner join users on users.id = channels.administratorId "
+ "order by channels.createdOn "
+ "OFFSET @Offset ROWS "
+ "FETCH next @PageSize Rows ONLY\n"
+ "select COUNT(*) from channels";
int offset = (page - 1) * pageSize;
IEnumerable <ListChannelsQueryResult> result;
using (SqlMapper.GridReader gridReader = await _connection.QueryMultipleAsync(query, new
{
PageSize = pageSize,
Offset = offset
}))
{
result = gridReader.Read <ListChannelsQueryResult, ListChannelsUsersJoinResult, ListChannelsQueryResult>(
(channel, administrator) =>
{
channel.Administrator = new ListUsersQueryResult
{
Id = administrator.AdministratorId,
Username = administrator.AdministratorUsername
};
return(channel);
},
splitOn: "administratorId"
);
int totalCount = await gridReader.ReadFirstAsync <int>();
return(new PagedList <ListChannelsQueryResult>(page, pageSize, totalCount, result));
}
}
public virtual Task <object> ReadFirstAsync() => _gridReader.ReadFirstAsync();
public Task <object> ReadFirstAsync()
{
return(_reader.ReadFirstAsync());
}