SQL to LINQ with sub joins and sub queries

Question

I am fairly new to LINQ and having issues when converting this SQL to LINQ. Can any one help me achieve this?

Select DISTINCT u.ID from 
(Select x.ID from table1 x where x.Info = 0) u
where u.ID not in 
(select c.ID from table1 c where c.Info = 1)

I have a table table 1

ID Info 1 0 1 1 1 0 2 0 3 1 3 0 4 0 5 1

I want to return all records that have all Info = 0 so the output for the above table would be 2, 4

Please share some insights

Answer 1

While you selected Octaviocci's answer, I would point out there is LINQ that looks like SQL than there are those that use Lambda expressions like his. Here is what a regular Linq statment might look like when your more familiar with T-SQL:

var nonEquijoinQuery = (from a in table1
                        let b = from c in table1
                            where c.Info == 1
                            select c.ID
                        where a.Info == 0 && b.Contains(a.ID) == false
                        select a.ID).Distinct();