CODEWITHSUNDEEP
linq-to-sql
Romek
Romek

Reputation: 11

linq to sql where in

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

Upvotes: 1

Views: 124

Answers (2)

Ray
Ray

Reputation: 46605

You should be able to use join for this.

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

Upvotes: 0

Tom Squires
Tom Squires

Reputation: 9296

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;
}

Upvotes: 1

Related Questions