How to do in Linq: "select * from Employee where ID in (10,11,12)"

Question

Dear Linq,
is there a more elegant way in Linq to perform this query?
Let's say I have an array of ID's that I am interested in,
int[] employeesToSelect = {10,11,12};

A naive attempt, which does compile:

from e in db.Employees
where e.ID==10 || e.ID==11 || e.ID==12
select e;


I can't get more sophisticated approaches to compile, for example:
var q2 = from e in db.Employees
   where Array.IndexOf<int>(employeesToSelect , e)
       select e;

Answer 1

Hi, this is an incarnation of one problem that already appeared here. You can look at the following threads:

• http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=731496&SiteID=1
• http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=582495&SiteID=1

I tried to solve similar problem (for string keywords) by writing library that enables you to write code like this:

// Select products, that conain one of values from array in their name
var q1 = from p in db.Products.ToExpandable()
    where p.ProductName.ContainsAny("Sir", "Chef")
    select new { p.ProductName };

I think it wouldn't be difficult to modify it to allow searching in array of any type. You can find it here: http://tomasp.net/blog/linq-expand-update.aspx.

Answer 2

There is one simple way to implement your needs like this:
IList<Employee> employees = db.Employees.ToList<Employee>();

var query = from e in employees
                   where employeesToSelect.Contrains(e.ID)
                   select e;
IList<Employee> result = query.ToList<Employee>();

Please note the first code line, why should I get the IList<Employee> object by converting the db.Employee? Because  method Boolean Contains(System.Int32) has no supported translation to SQL when employees is Table type. It will throw the NotSupportedException. If converting to the collection type, the problem will be solved.

Answer 3

 

Code Snippet

DataClasses1DataContext db = new DataClasses1DataContext();

int[] myInts = {10,11,12};

var q =

  from x in db.ProcessLogs

  where myInts.Contains(x.ProcessLogID)

  select x;

Console.WriteLine(db.GetCommand(q).CommandText);

Console.ReadLine();

 

 

 

 

SELECT [t0].[ProcessLogID], [t0].[ProcessQueueID], [t0].[ProcessLogTime], [t0].[
ProcessStatus], [t0].[ProcessStatusDetail]
FROM [dbo].[ProcessLog] AS [t0]
WHERE [t0].[ProcessLogID] IN (@p0, @p1, @p2)

Answer 4

There's an upper limit on the list length (SQL Server will not handle more than ~2000 parameters, but LINQ will happily generate them).

 

 

What's up with the bad answers from the MVP's?

Answer 5

Sorry, I am confused what's your mean. In the code snippet you sumbit, if db.ProcessLogs is Table Type, it will throw the NotSupportedException absolutely.

Would you please point out my error? Thanks.

Answer 6

Run the code.

Answer 7

Well, well. I know the big difference between our code. You are right, but I am not wrong David, if you try to change the in params "int[]" to IList<int> type, and run the code, what happen? It occurs the error what I said.

Forgive me, I didn't notice the example George posted in which the in params is array type. Because I always use the IList type, array type instead.

It's a very strange error. I am careless. Thank you.

Answer 8

Ah.  That's an odd case.

 

int[] works.

List<int> works (this is what I usually use)

 

IList<int> doesn't work??  Very strange.

Answer 9

My god. I don't really notice List<int> can work!

Right, IList<Int> can't work absolutely. It's really strange.

Answer 10

IEnumerable<int> list = new List<int>() { 1, 2, 3 };

List<Employee> query =     
      (from e in db.Employees
       where list.Contains(e.Id)
       select e).ToList();

LINQ 2 SQL didn't implement IN functionality in IList interface; instead, it's implemented using IEnumerable interface. As a result, you have to "cast" it. Since all generic collection implements IEnumerable, it shouldn't be too big of a deal.