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'.
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#)
------------------------------------------------------------------------
