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 async Task <IList <ArticleDataResult> > FindArticlesAsync(string searchPattern, IEnumerable <string> roles) { var result = new List <ArticleDataResult>(); using (NpgsqlConnection connection = new NpgsqlConnection(base.connectionString)) { string sql = @"WITH RECURSIVE ""RecursiveCategories"" AS ( SELECT ""Id"", ""ParentId"", ""Name"" FROM ""Categories"" UNION SELECT c.""Id"", c.""ParentId"", c.""Name"" FROM ""Categories"" c JOIN ""RecursiveCategories"" rc ON rc.""Id"" = c.""ParentId"" ) SELECT DISTINCT a.""Id"", a.""Title"", a.""Timestamp"" FROM ""RecursiveCategories"" rc JOIN ""ArticleCategories"" ac ON ac.""CategoryId"" = rc.""Id"" JOIN ""Articles"" a ON a.""Id"" = ac.""ArticleId"" LEFT JOIN ""ArticleRoles"" ar ON ar.""ArticleId"" = a.""Id"" LEFT JOIN ""AspNetRoles"" r ON r.""Id"" = ar.""RoleId"" WHERE ( (a.""Title"" ILIKE('%' || :SearchPattern || '%')) OR (a.""Body"" ILIKE('%' || :SearchPattern || '%')) OR (rc.""Name"" ILIKE('%' || :SearchPattern || '%')) ) AND (r.""Name"" IS NULL OR r.""Name"" = ANY(:Roles)) ORDER BY a.""Timestamp"", a.""Title"" "; using (NpgsqlCommand command = new NpgsqlCommand(sql, connection)) { command.Parameters.AddWithValue("SearchPattern", searchPattern); 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()) { var item = new ArticleDataResult { Id = (Guid)dataReader["Id"], Title = (string)dataReader["Title"] }; result.Add(item); } } } } return(result); }