LINQ and "WHERE id IN (...)" clause


O

Oleg Ogurok

Hi there,

Is there an equivalent of "WHERE .. IN (...)" clause in LINQ when
querying a SQL database?

E.g. I have list of IDs and I need to get the corresponding table
records.

Thanks,
-Oleg.
 
Ad

Advertisements

F

Frans Bouma [C# MVP]

Oleg said:
Hi there,

Is there an equivalent of "WHERE .. IN (...)" clause in LINQ when
querying a SQL database?

E.g. I have list of IDs and I need to get the corresponding table
records.
List<string> myIDs = new List<string>() { "CHOPS", "BLONP"};
NorthwindContext nw = new NorthwindContext();
var q = from o in nw.Orders
where myIDs.Contains(o.CustomerID)
select o;

Yes, this is backwards, but it's how the Linq designers thought
everyone should use databases these days.

I have no idea why there's no such thing like:
var q = from o in nw.Orders
where o.CustomerID in myIDs
select o;

I mean, would that have been so incredibly bad? No it would have been
better IMHO because it would be more natural for people who know SQL.

Now, for the 1000$ question, how to do a query like:
SELECT * FROM Orders
WHERE CustomerID IN
(
SELECT CustomerID FROM Customers WHERE Country = @country
)

if you don't have the list in-memory?
I have no idea.

Btw, the 'Contains' is an extension method on list and handled by the
Linq provider to produce SQL. It thus depends on the Linq provider if
this results in an IN() query.

Next week: how to do a left join in linq. No, not with 'left join'. :p

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Ad

Advertisements

M

Melle

This reaction is a bit late, but for the sake of others looking for a solution, this will work and resembles the WHERE Id in (..) clause pretty well.

from x in SomeTable
where (new Int32[] {100,101,102}).Contains(x.Id)
select x

Regards,

Melle
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complex Linq Query, building the Where Clause 2
Linq Entity question regarding Where clause 1
LINQ Where extension override 0
LinQ 1
LINQ 1
"LINQ to SQL" and "LINQ to Entity" 1
LINQ question 5
LINQ and Informix 0

Top