public async Task <ArticleDataResultPagedItemsList> GetLatestArticleItemsPagedAsync(int pageNumber, int pageSize, IEnumerable <string> roles)
{
var articleDataResultPagedItemsList = new ArticleDataResultPagedItemsList(pageNumber, pageSize);
using (NpgsqlConnection connection = new NpgsqlConnection(base.connectionString))
{
string sql = @"SELECT
COUNT(1) OVER() AS ""ResultsCount"",
a.""Id"",
a.""Title"",
a.""CoverFileId""
FROM ""Articles"" a
LEFT JOIN ""ArticleRoles"" ar ON ar.""ArticleId"" = a.""Id""
LEFT JOIN ""AspNetRoles"" r ON r.""Id"" = ar.""RoleId""
WHERE r.""Name"" IS NULL OR r.""Name"" = ANY(:Roles)
ORDER BY ""Timestamp"" DESC
LIMIT :PageSize
OFFSET(:PageNumber - 1) * :PageSize;
";
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("PageNumber", pageNumber);
command.Parameters.AddWithValue("PageSize", pageSize);
var rolesParam = new NpgsqlParameter("Roles", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
rolesParam.Value = roles;
command.Parameters.Add(rolesParam);
await connection.OpenAsync();
using (NpgsqlDataReader dataReader = await command.ExecuteReaderAsync())
{
while (await dataReader.ReadAsync())
{
if (articleDataResultPagedItemsList.TotalResultsCount == 0)
{
articleDataResultPagedItemsList.TotalResultsCount = Convert.ToInt32(dataReader["ResultsCount"]);
}
var item = new ArticleDataResult(dataReader);
articleDataResultPagedItemsList.AddItem(item);
}
}
}
}
return(articleDataResultPagedItemsList);
}
public HomeViewModel(ArticleDataResultPagedItemsList articleDataResultPagedItemsList)
: this(articleDataResultPagedItemsList.Items)
{
this.TotalPagesCount = articleDataResultPagedItemsList.TotalPagesCount;
this.PageNumber = articleDataResultPagedItemsList.PageNumber;
}
