Use of where in multiple joins to remove rows - linq
- by bergin
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,
};
