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

[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.

[Frans Bouma [C# MVP]]

Post by 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.

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

[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

Post by 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.

Post by Frans Bouma [C# MVP]
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.
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.
SELECT * FROM Orders
WHERE CustomerID IN
(
)
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#)
------------------------------------------------------------------------
Submitted via EggHeadCafe
ASP.NET Drawing a chart using OWC11 - Office Web Components
http://www.eggheadcafe.com/tutorials/aspnet/601e9bc2-40ed-405e-b1b0-f416046b6698/aspnet-drawing-a-chart-using-owc11--office-web-components.aspx