How to write this query in Linq2Sql

Question

I have a table company which holds the company_id, company_name and other details. I have a table subcontracts which has a company_id column that maps to company.company_id.

How can I write a select statement to get all active companies which have not been assigned to an active subcontract? IE The company_id cannot be found in subcontracts.company_id

******EDIT*****

I believe the correct sql is:

select company_id 
from company 
where company.active_status = 1 
and not exists( select * from subcontracts 
                where subcontracts.company = company.company_id 
                AND subcontracts.active_status = 1
              )

Answer 1

The sub-select is pretty much the same in LINQ.

var noSubs = from company in context.Companies
             where company.active_status == 1 &&
                 !(from subcontract in context.Subcontracts
                  where subcontract.active_status == 1
                  select subcontract.company_id).Contains(company.company_id)
             select company;

Linq to SQL will translate this as a "not exists" on the subcontract table.

Answer 2

Figure out how you would do this in standard SQL and then pick up a copy of Linqer (http://www.sqltolinq.com/). This product will convert almost any SQL statement into a LINQ query. It's not free, but not expensive either, and comes with a 30 day trial period. I have found it to be extremely useful.