LinqToSQL In Clause

Question

I'm trying to use LinqToSQL to generate a IN clause that could contain a lot of items. My original code was something like:

context.Employees.Where(p => EmployeeIDs.Contains(p.EmployeeID));

Which ends up generating an in clause with each employee id as a SQL parameter. The problem is if you go over 2100 parameters, an exception is thrown. I've worked around this elsewhere in my application by executing multiple queries and combining the results. That works ok in simple cases, but I'm doing something a little more complex now and I don't think this is the best solution.

Anyway, the Contains generates something like

EmployeesID IN (@p0, @p1, @p2)

But is there a way to convince it to do this?

EmployeesID IN (23, 582, 3948)

I started going down the path of using the dynamic query example, but then realized that it seems to be only for writing dynamic linq code, not dynamic sql. Then I found ExecuteQuery, but I don't want to build my entire query string manually, just the particular sections of the query that I want.

To be more specific, I'd like something like:

context.Employees
    .Where(p => p.Active == true)
    .Where("EmployeeID IN (23, 582, 3948)");

Or preferrably (if this would generate IN without using parameters):

context.Employees
    .Where(p => p.Active == true)
    .Where(p => p.In("EmployeeID", EmployeeIDs));

Any way to do this? Thanks.

KeithS · Answer 1 · 2011-01-26T21:11:06.897

Is it possible for you to define a query that will return the employee IDs needed based on some other query? If so, you can define the search using a nested query:

var employeeIds = from x in context.EitherEmployeesOrSomeOtherRecords
   where x.SomeConditionIsTrue
   select x.EmployeeID; //not yet evaluated; employeeIds is an IQueryable

var employees = from e in context.Employees
   where employeeIds.Contains(x.EmployeeID)
   select e;

This LINQ query will be digested by Linq2SQL into a single SQL statement using an EXISTS clause to check the employeeIds subquery's SQL equivalent.

As far as forcing Linq2SQL to specify values from code as literals, I don't think you can, but I could be wrong.

EDIT: OK then, what if you made the logic determining how to construct the query smarter? Like, if you have a "select all" button or checkbox, you could use that to craft a simpler query, "pull up all employees that would have been selected for the list":

var employees = from e in context.Employees
   where TheSameConditionThatCausedItToBeInTheList == true
   select e;

If the user selected all and then deselected a few, you could detect that (more selected than not) and instead pull up "all employees EXCEPT X, Y, and Z":

var notChecked = uiList.Items.Except(uiList.SelectedItems).Select(x=>x.EmployeeID)

var employees = from e in context.Employees
   where !notChecked.Contains(e.EmployeeID)
   select e;

If you have more than 4200 employees, the above won't always work (you could select more than 2100 but leave more than 2100 unselected, so it blows up either way), but I also can't imagine a list of 4200 items that doesn't also allow you to select based on some other metric (i.e. department), allowing a query like "pull up all employees in X, Y and Z departments, and also employees A, B and C":

var metaSelection = departmentList.SelectedItems.Select(x=>x.DepartmentID);

var exceptions = uiList.SelectedItems.Where(x=>!metaSelection.Contains(x.DepartmentID))
                       .Select(x=>x.EmployeeID);

var employees = from e in context.Employees
    where metaSelection.Contains(e.DepartmentID)
    || exceptions.Contains(e.EmployeeID)

You might include another version of your example using a `join` instead of a nested query. Just a suggestion, since I was posting something similar to yours. — shaunmartin, Jan 26 '11 at 20:39
The employee ids in my case are provided by the user. They see a list of Employees and select which ones they want. So I don't think this would work. — Dan, Jan 26 '11 at 20:44
I thought about trying to make the query smarter like you suggest. I'm not sure that will work too well. I see the potential for records to get included that weren't what the user wanted. For instance, the user sees a list of all active employees, next someone else updates the active data, then the first user does something to see results and it now includes the wrong employees. — Dan, Jan 26 '11 at 21:22

score 0 · Answer 2 · answered Jan 26 '11 at 21:31

The problem here is the 2100 parameters restriction. This is a hard limit defined by SQL. Not ADO.NET or LINQ. I ran into this wall myself a couple weeks ago. Since LINQ will always parameterize your queries, there is no way around it. You will have to batch your calls or use ExecuteQuery. But if you use ExecuteQuery, make sure you do your own validation on the parameters to avoid SQL injection attacks.

LinqToSQL In Clause

2 Answers2