LINQ left outer join

[John Thomas]

The following 2 LINQ statements give me my desired results but would like to
do it all in the 1st only a Left Outer Join, so I'm not returning unneeded
data.

The desired results are Invoices where IsPaid is false OR IsPaid is true but
payments are applied to them, which is what second statment filters for.


Dim invAll = (From c In db.ARIentries _
Where c.CustomerID = cusID _
Group Join i In db.ARIPayments On i.InvoiceID Equals
c.KeyID _
Into Payment = Group _
From i In Payment.DefaultIfEmpty _
Select c.InvoiceDate, c.InvoiceNumber, c.Amount,
c.Balance, c.KeyID, c.IsPaid, i.Payment).ToArray


Dim inv = (From i In invAll _
Where i.IsPaid = False Or i.Payment > 0 _
Select i).ToArray

Thanks John

 

[Patrice]

Uneeded data being ? Group join is used on purpose or just to get the
filtering you want ?

It seems to me that using a join would return uneeded data (you'll have a
line for each invoice AND payment)

Assuming you want a single line fo each invoice, you could do something such
as :


Dim q = From i In dc.Invoices Where i.Payments.Sum(Function(p) p.Amount) >0

or

Dim q = From i In dc.Invoices Where i.Payments.Any

(I just show the test on payments as the other test is crystal clear).