Как в SQLite сделать сложный запрос с циклом

В SQLite каждый запрос оборачивается в транзакцию, что сильно замедляет выполнение множества запросов.
В Frequently Asked Questions есть пункт про INSERT - нужно обернуть несколько запросов в одну транзакцию.
Благодаря этому вставка ста тысяч строк выполняется менее чем за секунду.

public void InsertData()
{
    var sql = "INSERT INTO [dbUserTable] ([IsOnline], [Name]) values (@isonline, @name);";

    using (var conn = new SQLiteConnection("Data Source=TestDB.db; Version=3;"))
    using (var cmd = new SQLiteCommand(sql, conn))
    {
        conn.Open();
        using (var tran = conn.BeginTransaction())
        {
            for (int i = 0; i < 100000; i++)
            {
                cmd.Parameters.Add("isonline", DbType.Int32).Value = 0;
                cmd.Parameters.Add("name", DbType.String).Value = "Name" + i;

                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
    }
}

К сожалению, это не помогает при выполнении UPDATE и DELETE.
Также к сожалению, в SQLite нет табличных параметров (table-valued parameters). Обычно с их помощью в других СУБД выполняются такие запросы.

Можно поступить так:

public async Task UpdateOnlineStatus(List<string> names)
{
    var parameters = string.Join(",", names.Select((_, i) => "@p" + i));
    string sql = $"UPDATE [dbUserTable] SET [IsOnline] = 1 WHERE [Name] IN ({parameters})";

    using (var connect = new SQLiteConnection("Data Source=TestDB.db; Version=3"))
    using (var command = new SQLiteCommand(sql, connect))
    {
        for (int i = 0; i < names.Count; i++)
        {
            command.Parameters.Add("p" + i, DbType.String).Value = names[i];
        }
        await connect.OpenAsync().ConfigureAwait(false);
        await command.ExecuteNonQueryAsync().ConfigureAwait(false);
    }
}

Здесь формируется длинный список параметров @p0,@p1,@p2 для использования в WHERE IN.
Конечно, очень длинный список использовать не стоит.
Я попробовал этот код с десятью тысячами значений - проблем не возникло.
Выполнение запроса с 10000 параметров на моей системе выполняется менее, чем за 2 секунды. Это с SSD. С HDD наверняка будет несколько медленней.

Действительно, если попытаться задать 50 тысяч параметров, то выбрасывается исключение:

SQL logic error too many SQL variables

В sqlite есть ограничение Maximum Number Of Host Parameters In A Single SQL Statement, которое по умолчанию равно 999 для версий до 3.32.0 или 32766 для версий после 3.32.0.
Также есть ограничение Maximum Length Of An SQL Statement, равное миллиарду байтов, но оно нам вряд ли грозит.

Естественно, желательно пользоваться самой новой версией Sqlite. Впрочем это не поможет обойти порог в 32766 параметров.
Как это можно обойти?

Конечно, можно разбивать один большой запрос на несколько более мелких, с количеством параметров не более 32766. Код писать не буду, думаю, каждый желающий сам справится.

Предложу ещё одно решение, с временной таблицей.

public async Task UpdateOnlineStatus(List<string> names)
{
    using (var conn = new SQLiteConnection("Data Source=TestDB.db; Version=3;"))
    {
        await conn.OpenAsync();

        var createSql = "CREATE TEMP TABLE [params] ([Name] text)";
        using (var createCmd = new SQLiteCommand(createSql, conn))
        {
            await createCmd.ExecuteNonQueryAsync();
        }

        var insertSql = "INSERT INTO [params] ([Name]) values (@name)";
        using (var insertCmd = new SQLiteCommand(insertSql, conn))
        {
            var nameParam = insertCmd.Parameters.Add("name", DbType.String);
            using (var tran = conn.BeginTransaction())
            {
                for (int i = 0; i < names.Count; i++)
                {
                    nameParam.Value = names[i];
                    await insertCmd.ExecuteNonQueryAsync();
                }
                tran.Commit();
            }
        }

        var updateSql = "UPDATE [dbUserTable] SET [IsOnline] = 1 WHERE [Name] IN (SELECT [Name] FROM [params])";
        using (var updateCmd = new SQLiteCommand(updateSql, conn))
        {
            await updateCmd.ExecuteNonQueryAsync();
        }
    }
}

Что здесь происходит. Открываем соединение - вся дальнейшая работа происходит с ним.
Создаём временную таблицу: CREATE TEMP TABLE.
В рамках одной транзакции в эту таблицу вставляем параметры. Это, как мы уже знаем, происходит очень быстро.
Далее выполняем запрос UPDATE с использованием этой временной таблицы.

Проверил на 50 тысячах параметров. Работает очень быстро. Думаю, это лучший вариант.

Примечания.

Вы используете библиотеку System.Data.SQLite, это понятно по именам SQLiteConnection, SQLiteCommand. Я бы предложил попробовать провайдер Microsoft.Data.Sqlite - более новый и легковесный (по заверениям разработчиков). Возможно, он будет чуть быстрее.
Но это в любом случае несущественно.

Sqlite не поддерживает асинхронный I/O. Async limitations. Поэтому нет смысла в вызове асинхронных методов.