Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq to SQL Nested IN FROM query

Tags:

linq-to-sql

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?

like image 819
mrtaikandi Avatar asked Dec 15 '08 09:12

mrtaikandi


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;
like image 57
Jon Skeet Avatar answered Nov 19 '22 23:11

Jon Skeet


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

like image 43
David Wengier Avatar answered Nov 20 '22 00:11

David Wengier


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
like image 6
MikeM Avatar answered Nov 19 '22 23:11

MikeM