public async Task <FilteredUsersModel> GetUsers(UserFilter filter) { filter ??= new UserFilter(); string orderBy = UserFilter.UserFilterOrderByToSqlColumn(filter.OrderBy); string sort = UserFilter.UserFilterSortToSql(filter.Sort); string offsetSql = string.Empty; var postgresParameters = new List <NpgsqlParameter>(); if (filter.Offset > 0) { offsetSql = "OFFSET @offset"; postgresParameters.Add(new NpgsqlParameter("offset", filter.Offset)); } string limitSql = string.Empty; if (filter.Limit > 1) { limitSql = "LIMIT @limit"; postgresParameters.Add(new NpgsqlParameter("limit", filter.Limit)); } string sql = $"SELECT * FROM users ORDER BY {orderBy} {sort} {offsetSql} {limitSql};"; var filteredUsers = await this.Database.Query <UserPoco>(sql, postgresParameters.ToArray()); // We clone the parameters because the original ones belong to the filtered query int usersCount = await this.Database.Execute <int>( $"SELECT COUNT(*)::int FROM users;", postgresParameters.Select(x => x.Clone()).ToArray() ); return(new FilteredUsersModel { Users = filteredUsers.ToArray(), Filter = filter, UsersCount = usersCount }); }