How to write this query in Linq2Sql

Answer 1

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.

Answer 2

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;

`

Answer 3

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));

Answer 4

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.

Answer 5

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.