How to write this query in Linq2Sql

I have a table company which holds the company_id, company_name and other details. I have a table subcon开发者_StackOverflow中文版tracts 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
              )

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.

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.

This should work:

var noContracts =
    from c in db.Companies
    join sc in db.Subcontracts.Where(sc => sc.active_status == 1) on c.company_id equals sc.company_id into compGroup
    from cg in compGroup.DefaultIfEmpty() 
    where cg.company_id == null
    select c;  

This does a LEFT OUTER JOIN. All subcontracts without a corresponding company_id will be assigned a NULL value for company_id, which it then selects.

Sounds like you are trying to do a WHERE NOT IN, like maybe:

var companiesWithoutSubcontracts = 
        from noSub in Companies
        where !(from withSub in Companies
                select withSub.company_id)
               .Contains(noSub.company_id)
        select noSub;

`

I have not tested it and it may well be that LINQ to SQL fails to translate the query, but in theory this should work.

var result = context
   .Subcontracts
   .Select(subcontract => new
      {
         Subcontract = subcontract,
         NotAssignedCompanies = context
            .Companies
            .Where(company => !company.Subcontracts.Contains(subcontract))
      });

This will return all not assigned companies for all subcontracts. If you only need the information for one specific subcontract, the following query will be sufficient.

var notAssignedCompanies = context
   .Companies
   .Where(company => !company.Subcontracts.Contains(specificSubcontract));