public async Task <IEnumerable <CountdownAggregate> > GetAggregatesAsync(CountdownFilter filter) { using (IDbConnection connection = Connection) { string sql = $@"SELECT TOP {filter.Limit} Q.* FROM ( SELECT T.*, ROW_NUMBER() OVER ({BuildAggregateOrderBy(filter.DisplayOrderType)}) AS RN FROM ( SELECT c.Id, c.Description, c.CreatedByAccountId, c.CreatedOn, c.EndsOn, a.DisplayName AS CreatedByDisplayName, a.Email AS CreatedByEmail, ISNULL((SELECT SUM(Value) FROM Votes WHERE CountdownId = c.Id), 0) AS VoteScore, (SELECT Value FROM Votes WHERE CountdownId = c.Id AND CastedByAccountId = @CurrentUserAccountId) AS CurrentUserVote, (SELECT COUNT(Id) FROM Comments WHERE CountdownId = c.Id) AS CommentsCount, (CASE WHEN CreatedByAccountId = @CurrentUserAccountId THEN 1 ELSE 0 END) AS IsCreatedByCurrentUser FROM Countdowns c INNER JOIN Accounts a ON c.CreatedByAccountId = a.Id WHERE c.EndsOn >= ISNULL(@EndsAfter, EndsOn) AND c.CreatedByAccountId = ISNULL(@CreatedByUserAccountId, CreatedByAccountId) AND c.Description LIKE @query ) AS T ) AS Q WHERE Q.RN > {(filter.Page - 1)*filter.Limit} ORDER BY Q.RN"; return(await connection.QueryAsync <CountdownAggregate>(sql, new { filter.CurrentUserAccountId, filter.SubFilter.EndsAfter, filter.SubFilter.CreatedByUserAccountId, query = $"%{filter.SubFilter.Query}%" })); } }
public Task <IEnumerable <CountdownAggregate> > GetAggregatesAsync(CountdownFilter filter) { return(Task.Run(() => CountdownAggregates.AsEnumerable())); }