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?
Add a comment
|
3 Answers
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;
2 Comments
David Wengier
There needs to be a "someone else is typing exactly the same answer as you" status popup on this site :)
Amy B
Reason to not join: If x is 1 to many with y, the join will give duplicated x's.
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
1 Comment
Dimse
What does this mean for performance? Will there be a difference in defining the query contra using it inline?
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
1 Comment
RianBattle
this answer helped me a lot, thanks! I have a class setup where one class holds a List(Of Class2) of which each hold a List(Of Class3) (to mimic a container, goods receiving voucher, and goods receiving voucher lines in a warehouse facility). Need to find a way to look for a specific part in Class3 while starting at Class1 without For Each looping through every class instance. This works great (without the Not for me)!