Can anyone tell me how to write a nested SQL query like
SELECT * FROM X WHERE X.ID IN (SELECT Y.XID FROM Y WHERE .....)
in LINQ?
You could try:
var yIds = from y in dataContext.Y
where ...
select y.XId;
var query = from x in dataContext.X
where yIds.Contains(x.Id)
select x;
I don't know whether it will work though - any reason why you don't want to just do a join instead? For instance:
var query = from x in dataContext.X
join y in dataContext.Y.Where(...) on x.Id equals y.Xid
select x;
To do an IN in sql, you need to use the Contains function in Linq.
So for example:
var query = from x in GetX()
where (from y in GetY() select y.xID).Contains(x.xID)
select x;
You could also define the inner linq query seperately if you like, which is a bit more readable
I was looking for a NOT IN
solution for LINQ to SQL. Thanks to this question I was able google the right thing and find this blog post: The NOT IN clause in LINQ to SQL
C#
NorthwindDataContext dc = new NorthwindDataContext();
var query =
from c in dc.Customers
where !(from o in dc.Orders
select o.CustomerID)
.Contains(c.CustomerID)
select c;
VB.net
Dim db As New NorthwinDataContext()
Dim query = From c In dc.Customers _
Where Not (From o in dc.Orders _
Select o.CustomerID).Contains(c.CustomerID) _
Select c
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With