LIMIT is not working in ActiveDataProvider

You can use Limit in the ActiveDataProvider and keep Pagination like so:-

$dataProvider = new ActiveDataProvider([
        'query' => $query,
         'pagination' => [
             'pageSize' => 50, 
         ],
    ]);
$dataProvider->setTotalCount(1000);

This will make $this->getTotalCount() = 1000 and Keep the pagination. Go here and here for more reference.

Here is what happens when preparing models in yii\data\ActiveDataProvider:

/**
 * @inheritdoc
 */
protected function prepareModels()
{
    if (!$this->query instanceof QueryInterface) {
        throw new InvalidConfigException('The "query" property must be an instance of a class that implements the QueryInterface e.g. yii\db\Query or its subclasses.');
    }
    $query = clone $this->query;
    if (($pagination = $this->getPagination()) !== false) {
        $pagination->totalCount = $this->getTotalCount();
        $query->limit($pagination->getLimit())->offset($pagination->getOffset());
    }
    if (($sort = $this->getSort()) !== false) {
        $query->addOrderBy($sort->getOrders());
    }

    return $query->all($this->db);
}

We're interested in this part:

if (($pagination = $this->getPagination()) !== false) {
    $pagination->totalCount = $this->getTotalCount();
    $query->limit($pagination->getLimit())->offset($pagination->getOffset());
}

So as you can see if pagination is not false, limit is managed automatically.

You can just set pagination to false and then manual setting of limit will work:

$dataProvider = new ActiveDataProvider([
    'query' => $query,
    'pagination' => false,
]);