linq to sql where in

Question

I want to translate query like this:

SELECT * FROM Product WHERE Product.ID in (SELECT Product.ID FROM other_table)

into LINQ. I read about using the contains method but the problem is that it generates a lot of parameters for each id passed in like this:

WHERE [Product].[ID] IN (@p0, @p1)

If I had for example one bilion parameters I want to pass into my query the server won't be able to execute such a long query. Is it possible to create LINQ query in such a way that the generated SQL will be close to the original?

Thanks, Romek

Are you against using a stored procedure? – Joe Jul 06 '11 at 16:08 — Joe, Jul 06 '11 at 16:08

score 1 · Answer 1 · answered Jul 06 '11 at 16:11

1

If you are using large tables then IN statments are a bad idea, they are very slow. You should be doing joins.

Anyway, here is what you want;

using(dbDataContext db = new dbDataContext())
{
var result = from p in db.products
             join o in db.other_table
             on p.ID equals o.ID
             select p;
}

answered Jul 06 '11 at 16:11

Tom Squires

8,848
12
46
72

Hi, thanks - unfortunately I cannot use this approach. Sorry I wasn't so clear about what I want to achieve. Generally I want to select products from one table with a given IDs. The list of id's will came from code (generated in C# as a result of some user operations). I don't know whether this is even possible to achieve. – Romek Jul 06 '11 at 16:25
Why would that prevent you from performing a join? If you only need to select rows with a certain id you don't need a join at all, just select them. – Ed S. Jul 06 '11 at 16:28

score 0 · Answer 2 · answered Jul 06 '11 at 16:13

0

You should be able to use join for this.

other_Table.Join(product, ot => ot.Id, pd => pd.Id, (pd, ot) => pd);

answered Jul 06 '11 at 16:13

Ray

45,695
27
126
169

linq to sql where in

2 Answers2