hi,

I have a table of orders. the status is on the soilorders which is joined to the orders. I only want to return orders where the joined soilorder does not have status "Removed".

I had thought that

   join sso in db.SoilSamplingOrders on ord.order_id equals sso.order_id
    where sso.status.Equals("Removed")!=true

but then no records are returned!

thanks for any help (query below)

var query = 

from ord in db.Orders

join sso in db.SoilSamplingOrders on ord.order_id equals sso.order_id
where sso.status.Equals("Removed")!=true

join cust in db.Customers on ord.customer_id equals cust.customer_id

select new Listing
{
    assigned_to = sso.assigned_to, 
    company = cust.company,
    order_id = ord.order_id,
    order_created = ord.order_created,
    customer_id = ord.customer_id,
    order_created_by_employ_id = ord.order_created_by_employ_id,
    first_farm_on_order =   (from f in db.SoilSamplingSubJobs 
                where f.order_id == ord.order_id
                    select new ListingSubJob { first_farm_on_order = f.farm }).
                    AsEnumerable().First().first_farm_on_order,
    total_fields = (from f in db.SoilSamplingSubJobs
        where f.order_id == ord.order_id
        select new   { f.sssj_id }).AsEnumerable().Count(),
    total_area = (float?) (from f in db.SoilSamplingSubJobs
        where f.order_id == ord.order_id && f.area_ha != null
        select  f.area_ha ).Sum() ?? 0 ,
    total_area_ph_density = (float?)(from f in db.SoilSamplingSubJobs
        where f.order_id == ord.order_id && f.ph != null
        select f.ph).Sum() ?? 0,
};