LINQ left outer join

Discussion in 'ASP .Net' started by John Thomas, Sep 1, 2009.

  1. John Thomas

    John Thomas Guest

    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
     
    John Thomas, Sep 1, 2009
    #1
    1. Advertising

  2. John Thomas

    Patrice Guest

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


    --
    Patrice


    "John Thomas" <> a écrit dans le message de groupe de
    discussion : ...
    > 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, Sep 1, 2009
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alan Silver
    Replies:
    0
    Views:
    889
    Alan Silver
    Jun 5, 2006
  2. john

    LINQ Join Error

    john, Feb 27, 2008, in forum: ASP .Net
    Replies:
    1
    Views:
    411
    Steven Cheng
    Feb 27, 2008
  3. George

    Linq or not Linq

    George, Nov 4, 2008, in forum: ASP .Net
    Replies:
    4
    Views:
    362
    Mike Gleason jr Couturier
    Nov 5, 2008
  4. PW

    Outer join problem

    PW, May 31, 2006, in forum: ASP General
    Replies:
    4
    Views:
    134
    Jason
    May 31, 2006
  5. Brian Candler
    Replies:
    1
    Views:
    185
    Brian Candler
    Mar 20, 2007
Loading...

Share This Page