LINQ to return only header items where line items are not zero

Question

I have a simple sales header / sales line item table and I would like to return all header items where there the total quantity of line items is not zero

I thought I could just do a join but that returns the header items duplicated for each line item:

    var orders = from so in _ctx.SalesOrders
                 join soi in _ctx.SalesOrderItems on so.SalesOrderID equals soi.SalesOrderID
                 where so.IsDeleted != true
                 && so.IsCompleted != true
                 && soi.IsDeleted != true
                 && soi.QtyOutstanding > 0
                 select so;

How do I write my linq so that I only get a list of header items where the qty outstanding is above zero for any line item?

Answer 1

You could wrap the Linq query syntax with a Dinstict() call:

(from so in _ctx.SalesOrders
join soi in _ctx.SalesOrderItems on so.SalesOrderID equals soi.SalesOrderID
where so.IsDeleted != true
&& so.IsCompleted != true
&& soi.IsDeleted != true
&& soi.QtyOutstanding > 0
select so).Distinct();

Alternatively, if you have navigation properties set up, you could use the regular Linq syntax without the need for a join or a Distinct():

var result = _ctx.SalesOrders
    .Where(so => 
        !so.IsDeleted && 
        !so.IsCompleted && 
        so.SalesOrderItems.Any(soi => !soi.IsDeleted && soi.QtyOutstanding > 0);