.net - 在 sql 查询中传递整数列表，最佳实践

Question

我目前正在寻找在 SQL 查询中传递整数列表的方法，并尝试确定哪种方法在哪种情况下最好，每种方法的优点是什么，陷阱是什么，应该避免什么:)

现在我知道我们目前在应用程序中使用的 3 种方式。

1) 表值参数:在 sql server 中创建一个新的表值参数:

CREATE TYPE [dbo].[TVP_INT] AS TABLE(
    [ID] [int] NOT NULL
)

然后对其运行查询:

using (var conn = new SqlConnection(DataContext.GetDefaultConnectionString))
{
    var comm = conn.CreateCommand();
    comm.CommandType = CommandType.Text;
    comm.CommandText = @"
UPDATE DA
    SET [tsLastImportAttempt] = CURRENT_TIMESTAMP
FROM [Account] DA
JOIN @values IDs ON DA.ID = IDs.ID";
    comm.Parameters.Add(new SqlParameter("values", downloadResults.Select(d => d.ID).ToDataTable()) { TypeName = "TVP_INT" });
    conn.Open();
    comm.ExecuteScalar();
}

此方法的主要缺点是 Linq 不支持表值参数(如果您使用 TVP 参数创建 SP，linq 将无法运行它):(

2) 将列表转换为二进制并在 Linq 中使用!这样好一点.. 创建一个 SP，你可以在 linq 中运行它:)

为此，SP 将有一个 IMAGE 参数，我们将使用用户定义的函数 (udf) 将其转换为表格。我们目前有用 C++ 和汇编语言编写的此函数的实现，两者的性能几乎相同:)基本上，每个整数由 4 个字节表示，并传递给 SP。在 .NET 中，我们有一个将 IEnumerable 转换为字节数组的扩展方法

扩展方法: public static Byte[] ToBinary(这个 IEnumerable intList) { 返回 ToBinaryEnum(intList).ToArray();

private static IEnumerable<Byte> ToBinaryEnum(IEnumerable<Int32> intList)
{
    IEnumerator<Int32> marker = intList.GetEnumerator();
    while (marker.MoveNext())
    {
        Byte[] result = BitConverter.GetBytes(marker.Current);
        Array.Reverse(result);
        foreach (byte b in result)
            yield return b;
    }
}

SP:

CREATE PROCEDURE [Accounts-UpdateImportAttempts]
    @values IMAGE
AS
BEGIN

UPDATE DA
    SET [tsLastImportAttempt] = CURRENT_TIMESTAMP
FROM [Account] DA
JOIN dbo.udfIntegerArray(@values, 4) IDs ON DA.ID = IDs.Value4

END

我们可以通过直接运行 SP 或在我们需要的任何 linq 查询中使用它

using (var db = new DataContext())
{
    db.Accounts_UpdateImportAttempts(downloadResults.Select(d => d.ID).ToBinary());
    // or
    var accounts = db.Accounts
        .Where(a => db.udfIntegerArray(downloadResults.Select(d => d.ID).ToBinary(), 4)
            .Select(i => i.Value4)
            .Contains(a.ID));
}

这种方法的好处是在 linq 中使用编译查询(它们将具有相同的 sql 定义和查询计划，因此也将被缓存)，并且也可以在 SP 中使用。

这两种方法理论上都是无限的，所以你一次可以传递数百万个整数:)

3) 简单的 linq .Contains()这是一种更简单的方法，在简单的场景中非常完美。但当然受限于此。

using (var db = new DataContext())
{
    var accounts = db.Accounts
        .Where(a => downloadResults.Select(d => d.ID).Contains(a.ID));
}

这种方法最大的缺点是downloadResults变量中的每个整数都将作为单独的int传递。在这种情况下，查询受sql限制(sql查询中允许的最大参数，这是一对千，如果我没记错的话)。

---

所以我想问一下.. 您认为其中最好的是什么，我还错过了哪些其他方法和方法？

Answer 1

您可以使用 XML 数据类型作为参数

DECLARE @input xml
SET @input = '<Inputs><Input>1</Input><Input>2</Input></Inputs>'

            SELECT
                Inputs.Input.value('.','int') Input
            FROM
                @input.nodes('/Inputs/Input) as Inputs(Input)