在LINQ-to-SQL的ExecuteQuery中使用IN子句 [英] Using an IN clause with LINQ-to-SQL's ExecuteQuery

问题描述

LINQ to SQL在翻译我的一个查询时做得很糟糕，所以我手工重写了它.问题在于重写必须包含IN子句，而我一生都无法弄清楚如何为此目的将集合传递给ExecuteQuery.我唯一能想到的，就是我在这里看到的建议，是在整个查询字符串上使用string.Format来包围它—但这将阻止查询最终在查询缓存中结束. /p>

什么是正确的方法?

注意:请注意，我正在使用传递给 ExecuteQuery的原始SQL.我在第一句话中说过.告诉我使用Contains并没有帮助，除非您知道将Contains与原始SQL混合的方法.

解决方案

表值参数

在Cheezburger.com上，我们经常需要将资产ID或用户ID的列表传递到存储过程或数据库查询中.

不好的方法:动态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，因此在DB端不需要进行额外的解码.

好方法:表值参数

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注入
3. 可缓存的稳定查询
4. 不锁定架构表
5. 不仅限于8k的数据
6. 由于没有CSV字符串的串联或解码，因此DB服务器和Mine应用程序都无需完成工作.
7. 典型使用"统计信息可以通过查询分析器得出，从而可以提供更好的性能.

缺点

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

进一步阅读

本文是了解TVP的好资源.

LINQ to SQL did a horrible job translating one of my queries, so I rewrote it by hand. The problem is that the rewrite necessarily involves an IN clause, and I cannot for the life of me figure out how to pass a collection to ExecuteQuery for that purpose. The only thing I can come up with, which I've seen suggested on here, is to use string.Format on the entire query string to kluge around it—but that will prevent the query from ever ending up in the query cache.

What's the right way to do this?

NOTE: Please note that I am using raw SQL passed to ExecuteQuery. I said that in the very first sentence. Telling me to use Contains is not helpful, unless you know a way to mix Contains with raw SQL.

解决方案

Table-Valued Parameters

On Cheezburger.com, we often need to pass a list of AssetIDs or UserIDs into a stored procedure or database query.

The bad way: Dynamic SQL

One way to pass this list in was to use dynamic 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);

This is a very bad thing to do:

1. Dynamic SQL gives attackers a weakness by making SQL injection attacks easier.
   Since we are usually just concatenating numbers together, this is highly unlikely, but if you start concatenating strings together, all it takes is one user to type ';DROP TABLE Asset;SELECT ' and our site is dead.
2. Stored procedures can't have dynamic SQL, so the query had to be stored in code instead of in the DB schema.
3. Every time we run this query, the query plan must be recalculated. This can be very expensive for complicated queries.

However, it does have the advantage that no additional decoding is necessary on the DB side, since the AssetIDs are found by the query parser.

The good way: Table-Valued Parameters

SQL Server 2008 adds a new ability: users can define a table-valued database type. Most other types are scalar (they only return one value), but table-valued types can hold multiple values, as long as the values are tabular.

We've defined three types: varchar_array, int_array, and bigint_array.

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

Both stored procedures and programmatically defined SQL queries can use these table-valued types.

  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));

Advantages

1. Can be used in both stored procedures and programmatic SQL without much effort
2. Not vulnerable to SQL injection
3. Cacheable, stable queries
4. Does not lock the schema table
5. Not limited to 8k of data
6. Less work done by both DB server and the Mine apps, since there is no concatenation or decoding of CSV strings.
7. "typical use" statistics can be derived by the query analyzer, which can lead to even better performance.

Disadvantages

1. Only works on SQL Server 2008 and above.
2. Rumors that TVP are prebuffered in their entirety before execution of the query, which means phenomenally large TVPs may be rejected by the server. Further investigation of this rumor is ongoing.

Further reading

This article is a great resource to learn more about TVP.

这篇关于在LINQ-to-SQL的ExecuteQuery中使用IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！