Динамический запрос к базе данных

Я работаю с C#, Npgsql, EF Core и Postgres.
Я определил конечную точку для поиска с разбивкой на страницы, где фильтры и столбец orderBy являются динамическими. Конечная точка принимает PaginationOptions экземпляр:

public class PaginationOptions
{
    public int Page { get; set; }
    public int ItemsPerPage { get; set; }
    public string OrderBy { get; set; }
    public bool Desc { get; set; }
    public IList<FilterValues> Filters { get; set; }
}

public class FilterValues
{
    public string FieldName { get; set; }
    public IEnumerable<string> Values { get; set; }
}

Следующий метод выполняет поиск и возвращает кортеж с отсортированными элементами и счетчиком общего количества элементов в таблице:

public Tuple<IList<T>, int> Search(PaginationOptions paginationOptions)
{
    if (!string.IsNullOrEmpty(paginationOptions.OrderBy))
    {
        CheckFilterField(paginationOptions.OrderBy);
    }

    int offset = (paginationOptions.Page - 1) * paginationOptions.ItemsPerPage;
    string orderBy = !string.IsNullOrEmpty(paginationOptions.OrderBy) ? paginationOptions.OrderBy : $"{prefix}.title";
    string order = paginationOptions.Desc ? "DESC" : "ASC";

    using (NpgsqlConnection connection = GetConnection())
    {
        string query = $"{GetQueryfields()} {GetFromClause()} {BuildWhere(paginationOptions.Filters)}";
        string itemsQuery = $"SELECT {query} ORDER BY {orderBy} {order}";

        NpgsqlCommand command = BuildCommand(connection, itemsQuery, paginationOptions.Filters);

        IDataReader reader = command.ExecuteReader();
        ISet<Guid> guids = new HashSet<Guid>(paginationOptions.ItemsPerPage);
        while (reader.Read())
        {
            Guid guid = reader.GetGuid(0);
            if (!guids.Contains(guid))
            {
                guids.Add(guid);
            }
        }
        ISet<Guid> filteredGuids = guids.Skip(offset).Take(paginationOptions.ItemsPerPage).ToHashSet();

        IList<T> items = GetItems(filteredGuids);

        return Tuple.Create(items, guids.Count);
    }
}

Проще говоря: в каждой сущности есть поля запроса и определение предложения FROM. Они разделены, потому что мне также нужно предложение FROM в другом методе.
WHERE (подготовленный оператор) и ORDER BY строятся динамически с использованием параметров. В BuildCommand создает NpgsqlCommand и устанавливает параметры. Затем я использую Dapper для необработанного запроса, чтобы получить идентификаторы запрошенных элементов, затем загружаю их с помощью EF и в конце я Skip и Take чтобы иметь правильную нумерацию страниц.
Проблема в том, что EF не позволяет добавлять предложение ORDER BY для необработанных запросов, оно доступно только через Linq выражение:

context.AnEntity.FromSqlRaw("Select * from users ORDER BY id").OrderBy(x => x.Title);

ORDER BY id игнорируется, элементы сортируются по выражению. Если выражение orderby linq не используется, фреймворк добавляет ORDER BY entity.id. В противном случае я мог бы сделать следующее:

string itemsQuery = $"SELECT {query} ORDER BY {orderBy} {order}";
context.AnEntity.FromSqlRaw(itemsQuery).Skip(offset).Take(limit)...

Оно работает. Даже для таблицы с 1 мил запрос занимает 2,8 секунды.
Комментарии? Намеки на улучшение?

Редактировать:
В итоге я получил запрос, который загружает выгружаемые данные за 2,2 секунды по таблице с 1 миллионными строками. Это приемлемый результат?

Просто быстрый выстрел в

IDataReader reader = command.ExecuteReader();
ISet<Guid> guids = new HashSet<Guid>(paginationOptions.ItemsPerPage);
while (reader.Read())
{
    Guid guid = reader.GetGuid(0);
    if (!guids.Contains(guid))
    {
        guids.Add(guid);
    }
}
ISet<Guid> filteredGuids = guids.Skip(offset).Take(paginationOptions.ItemsPerPage).ToHashSet();

Поскольку Guid-column является вашим столбцом первичного ключа, вы должны просто использовать List<Guid>. Вам не нужно будет проверять, есть ли reader.GetGuid(0) существует, потому что этого просто не может произойти.

Я бы не стал создавать первое HashSet<Guid> используя способность paginationOptions.ItemsPerPage но будет использовать фиксированное число, которое должно быть в нижнем диапазоне, но не слишком низко. Потому что, если вы используете этот перегруженный конструктор, вы должны использовать его так, чтобы он хорошо масштабировался. Имея низкое значение емкости, вы можете быстро достичь точки, когда не останется никаких слотов, что означает HashSet необходимо изменить размер. Если число для емкости слишком велико, инициализация может занять больше времени, поскольку создается два массива с длиной переданной емкости.

Взгляните на справочный источник для HashSet<T>

Это относится к List<T> также.

В конце я бы изменил код на

IDataReader reader = command.ExecuteReader();
List<Guid> guids = new List<Guid>(2000);
while (reader.Read())
{
    guids.Add(reader.GetGuid(0));
}
ISet<Guid> filteredGuids = guids.Skip(offset).Take(paginationOptions.ItemsPerPage).ToHashSet();

но извлечет 2000 к значимой именованной константе.

• [PaginationOptions] Переименовать Des к OrderByDescending и удалить OrderBy, это было бы более управляемо, так как у вас уже есть значение по умолчанию order by в вашем запросе.
• Я не вижу чек для полей? в противном случае перед построением запроса убедитесь, что каждое имя поля существует в вашей таблице.
• Использовать Dapper для подсчета и выбора запросов, поскольку они быстрее в dapper, чем EF.
• Поскольку это веб-API, Tuple будет излишним для вашего контроллера, вы можете подумать о возврате IEnumerable или IDictionary<int, IList<T>> или любой более простой тип, просто попробуйте упростить результаты для потребителя.
• вы можете использовать FETCH clause вместо LINQ Take и Skip, что сделало бы его быстрее, а также уменьшило бы выделенную память. ссылающийся на это страница чтобы узнать больше об этом.
  (К вашему сведению: PostgreSQL, SQL Server, Oracle и MySQL поддерживает FETCH clause с некоторыми оговорками).
• guids уже HashSet<Guid> поэтому проверка guid в цикле не требуется, так как guids.Add(guid) проигнорирует значение, если оно уже есть в коллекции.