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

Question

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

正确的做法是什么？

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

Answer 1

表值参数

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

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

好方法:表值参数

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

我们定义了三种类型:varchar_array、int_array 和 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 注入(inject)攻击
3. 可缓存、稳定的查询
4. 不锁定架构表
5. 不限于 8k 数据
6. 数据库服务器和 Mine 应用完成的工作量更少，因为没有对 CSV 字符串进行串联或解码。
7. 查询分析器可以导出“典型用途”统计信息，从而获得更好的性能。

缺点

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

进一步阅读

This article是了解有关 TVP 的更多信息的绝佳资源。