Using IN Clause with LINQ-to-SQL ExecuteQuery
LINQ to SQL did a terrible job translating one of my queries, so I rewrote it by hand. The problem is that the rewriting necessarily involves a sentence IN
, and I can't for the life of me figure out how to pass a collection in ExecuteQuery
for that purpose. The only thing I can suggest, which I saw here, is to use the string.Format
entire query string to swap around it, but this will prevent the request from completing in the query cache.
What is the correct way to do this?
NOTE . Please note that I am using raw SQL passed in ExecuteQuery
. I said it in the first sentence. Telling me to use is Contains
not helpful unless you know how to mix Contains
with raw SQL.
source to share
Tabular parameters
At Cheezburger.com, we often need to pass a list of AssetIDs or UserIDs to a stored procedure or database query.
The Bad Way: Dynamic SQL
One way to pass this list is using 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 very bad:
- Dynamic SQL gives attackers a weakness by making SQL injection attacks easier.
Since we usually just concatenate numbers together, this is unlikely, but if you start concatenating strings together, all it takes is one user typing and our site is dead.';DROP TABLE Asset;SELECT '
- Stored procedures cannot have dynamic SQL, so the query must be stored in code instead of the DB schema.
- Every time we run this query, the query plan must be recalculated. This can be very expensive for complex queries.
However, it has the advantage that no additional decoding is required on the DB side since the AssetIDs are found by the query parser.
The Good Way: Tabular Parameters
SQL Server 2008 adds a new feature: users can define the type of database based on tables. Most of the other types are scalar (they only return one value), but table types can contain multiple values if the values are table values.
We have identified 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 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));
<strong> Benefits
- Can be used in both stored procedures and programmatic SQL with little effort.
- Not vulnerable to SQL injection
- Cached, stable queries
- Does not lock the schema table
- Not limited to 8k data
- Less work done by both DB server and Mine applications as there is no CSV string concatenation or decoding.
- "Typical Usage" statistics can be obtained by the Query Analyzer, which can lead to even better performance.
disadvantages
- Only works with SQL Server 2008 and up.
- Rumors that TVPs are preloaded in full prior to the request being made, which means phenomenally large TVPs could 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.
source to share
If you cannot use table parameters, this option is slightly faster than the xml option, allowing you to stay away from dynamic sql: pass the concatenated list of values as a string parameter and parse the string delimiter to return the values to your query. see this article for instructions on effective analysis.
source to share
I have a suspicious suspicion that you are on SQL Server 2005. The table parameters were not added until 2008, but you can still use the XML datatype to transfer sets between client and server.
source to share
This works for SQL Server 2005 (and later):
create procedure IGetAListOfValues
@Ids xml -- This will recevie a List of values
as
begin
-- You can load then in a temp table or use it as a subquery:
create table #Ids (Id int);
INSERT INTO #Ids
SELECT DISTINCT params.p.value('.','int')
FROM @Ids.nodes('/params/p') as params(p);
...
end
You should call this procedure with a parameter like this:
exec IGetAListOfValues
@Ids = '<params> <p>1</p> <p>2</p> </params>' -- xml parameter
The nodes function uses the xPath expression. In this case it is /params/p
, and thus XML is used <params>
as root and <p>
as element.
The value function passes the text inside each element p
to int, but you can easily use it with other data types. There is a DISTINCT in this example to avoid duplicate values, but of course you can remove it depending on what you want to achieve.
I have a helper (extension) method that converts IEnumerable<T>
to a string that is similar to the one shown in the runtime example. It's easy to create it and let it work for you whenever you need it. (You have to check the datatype T and convert to an appropriate string that can be parsed on the SQL Server side). This way your C # code is cleaner and your SPs follow the same pattern to get parameters (you can pass as many lists as needed).
One of the advantages is that you don't need to do anything special in your database for it to work.
Of course, you don't need to create a temporary table as you did in my example, but you can directly use the query as a subquery inside the predicate IN
WHERE MyTableId IN (SELECT DISTINCT params.p.value('.','int')
FROM @Ids.nodes('/params/p') as params(p) )
source to share
I'm not 100% sure if I understood the problem correctly, but LinqToSql ExecuteQuery has an overload for parameters and the query should use a format similar to string.Format.
Using this overload is safe for SQL injection, and behind the scenes the LinqToSql translet uses its sp_executesql with parameters.
Here's an example:
string sql = "SELECT * FROM city WHERE city LIKE {0}";
db.ExecuteQuery(sql, "Lon%"); //Note that we don't need the single quotes
This way, you can take advantage of parameterized queries, even when using dynamic sql.
However, when it comes to using IN with a dynamic number of parameters, there are two options:
-
Build the string dynamically and then pass the values as an array, as in:
string sql = "SELECT * FROM city WHERE zip IN ("; List<string> placeholders = new List<string>(); for(int i = 0; i < zips.Length;i++) { placeholders.Add("{"+i.ToString()+"}"); } sql += string.Join(",",placeholders.ToArray()); sql += ")"; db.ExecuteQuery(sql, zips.ToArray());
-
We can take a more compact approach using Linq extension methods as in
string sql = "SELECT * FROM city WHERE zip IN ("+ string.Join("," , zips.Select(z => "{" + zips.IndexOf(f).ToString() + "}")) +")"; db.ExecuteQuery(sql, zips.ToArray());
source to share