Is it possible to prevent LINQ from using variables in queries and instead use string literals?

I have a LINQ query that has lots of variables and the generated SQL looks something like:

exec sp_executesql N'SELECT col1, col2
FROM MyTable [t3]
WHERE 
  (([t3].[col1] = @p0) AND ([t3].[col2] = @p1))
  OR (([t3].[col1] = @p2) AND ([t3].[col2] = @p3))
  ...
N'@p0 varchar(32),@p1 varchar(32),@p2 varchar(32),@p3 varchar(32),...
,@p0='0',@p1='1',@p2='2',@p3='3',...

This works fine as long as there are not too many variables, say 150 or less. But when there are too many variables, the query slows down significantly.

If I have the same query, except that there are no variables, like:

SELECT col1, col2
FROM MyTable [t3]
WHERE
  (([t3].[col1] = '0') AND ([t3].[col2] = '1'))
  OR (([t3].[col1] = '2') AND ([t3].[col2] = '3'))
  ...

Then the query works fine, even when there are many OR conditions.

While I believe this is really an issue with SQL Server behaving poorly when there are too many variables, I am wondering if I can workaround this issue in LINQ by not having the gene开发者_开发问答rated SQL contain references to variables, but instead use string literals.

I realize that I can workaround this issue by not using LINQ, and instead constructing the SQL myself, but hoping to have a LINQ solution.

Also, while this question is about LINQ, if there's a solution to speeding up the query which involves SQL Server, I'd be interested in that as well. I should add that I am seeing this issue with SQL Server 2005.

Thanks for your help, Eric

I don't believe so. In fact one of the features of Linq is that it prevents users from Sql Injection attacks.

You may have better performance if you load your query parameters into a table, and then run your query as a join against the table.

The question is very broad. It is likely that LINQ itself has nothing to do with your query performance.

If you have a lot of OR in the query this is what makes SQL query optimizer get confused on what index to use if any.

In my opinion you need to focus on the SQL query optimization first, add appropriate indexes. When SQL query is optimized you will not have issues with LINQ.

[EDIT]

If you use string literals, you might end up with worse performance, as SQL query optimizer will not be able to reuse query plans. Every query will trigger new query plan generation.

As others have stated, parameters is not the problem. Many ORs in the where clause can confuse SQL Server, whether the query is parameterized or not.

One workaround is to change your query to UNION query with one UNION for each OR.

E.g.:

select ... from MyTable as t0 where t0.col1 = 0 and t0.col2 = 1
  union all
select ... from MyTable as t1 where t1.col1 = 2 and t0.col2 = 3

You can easily build up a query similar to that in a loop using the LINQ .Union method:

List<Tuple<int, int>> IDPairs = new List<Tuple<int, int>> { new Tuple<int, int>(0,1), new Tuple<int, int>(2,3) }

IQueryable<MyTable> query = null;
foreach (Tuple<int, int> IDs in IDPairs)
{
  if (query == null)
  {
    query = dc.MyTable.Where(w => w.Col1 == IDs.Item1 && w.Col2 == IDs.Item2);
  }
  else
  {
    query = query.Union(dc.MyTable.Where(w => w.Col1 == IDs.Item1 && w.Col2 == IDs.Item2));
  }
}

var result = query.ToList();