将 IN 子句与 LINQ-to-SQL ExecuteQuery 结合使用

LINQ to SQL 在翻译我的一个查询时做得很糟糕，所以我手动重写了它。问题是重写必然涉及到IN子句，我一生都无法弄清楚如何将集合传递给ExecuteQuery为了这个目的。我唯一能想到的，我在这里看到的建议，就是使用string.Format在整个查询字符串上进行整理，但这将阻止查询最终出现在查询缓存中。

这样做的正确方法是什么？

NOTE: 请注意我正在使用传递给的原始 SQL ExecuteQuery。我在第一句话就这么说了。告诉我使用Contains没有帮助，除非你知道混合的方法Contains使用原始 SQL。

表值参数

在 Cheezburger.com 上，我们经常需要将 AssetID 或 UserID 列表传递到存储过程或数据库查询中。

坏方法：动态 SQL

传递此列表的一种方法是使用动态 SQL。

 IEnumerable<long> assetIDs = GetAssetIDs();
 var myQuery = "SELECT Name FROM Asset WHERE AssetID IN (" + assetIDs.Join(",") + ")";
 return Config.GetDatabase().ExecEnumerableSql(dr=>dr.GetString("Name"), myQuery);

这是一件非常糟糕的事情：

1. 动态 SQL 使 SQL 注入攻击变得更容易，从而给攻击者带来了弱点。
   由于我们通常只是将数字连接在一起，因此这种情况不太可能发生，但是 如果您开始将字符串连接在一起，只需一个用户输入';DROP TABLE Asset;SELECT '我们的网站已经死了。
2. 存储过程不能有动态SQL，所以查询必须是存储在代码中而不是数据库模式中。
3. 每次运行此查询时，都必须重新计算查询计划。对于复杂的查询来说，这可能非常昂贵。

然而，它确实有一个优点，即数据库端不需要额外的解码，因为 AssetID 是由查询解析器找到的。

好方法：表值参数

SQL Server 2008增加了一项新功能：用户可以定义表值数据库类型。 大多数其他类型都是标量（它们仅返回一个值），但表值类型可以保存多个值，只要这些值是表格形式的。

我们定义了三种类型：varchar_array, int_array, and bigint_array.

CREATE TYPE bigint_array AS TABLE (Id bigint NOT NULL PRIMARY KEY)

存储过程和以编程方式定义的 SQL 查询都可以使用这些表值类型。

  IEnumerable<long> assetIDs = GetAssetIDs();
  return Config.GetDatabase().ExecEnumerableSql(dr=>dr.GetString("Name"),
      "SELECT Name FROM Asset WHERE AssetID IN (SELECT Id FROM @AssetIDs)", 
      new Parameter("@AssetIDs", assetIDs));

优点

1. 无需太多努力即可在存储过程和编程 SQL 中使用
2. 不易受到 SQL 注入攻击
3. 可缓存、稳定的查询
4. 不锁定模式表
5. 不限于8k数据
6. 数据库服务器和 Mine 应用程序完成的工作较少，因为没有 CSV 字符串的串联或解码。
7. 查询分析器可以导出“典型用途”统计信息，这可以带来更好的性能。

缺点

1. 仅适用于 SQL Server 2008 及更高版本。
2. 有传言说 TVP 在执行查询之前会被完全预缓冲，这意味着服务器可能会拒绝非常大的 TVP。 对此谣言的进一步调查正在进行中。

进一步阅读

本文 http://www.sommarskog.se/arrays-in-sql-2008.html是了解更多有关 TVP 的重要资源。