LINQ outer join query

Hi Experts,

I am trying to create a LINQ statement that executes an outerjoin. I am not getting anywhere as the examples I have managed to see so far are all in C#.

I have TWO columns one called posting_id and the other link_posting_id.

There will ALWAYS be a posting_ID but in some instances there will also be a posting_id.

where there is a link_posting_id it will equal an earlier posting_id.

I wanted to create an LINQ statement that displays all the posting_Id's but at the same time group those lines with a link_posting_id with the identical Posting_id.

I had the idea that if you use an outer join you would obtain all the posting_ids and all the link_posting_ids directly UNDER each of the identical posting_ids...

I tried this as a first attempt:

Dim test3= From ledg1 In db.ledgers _
                           Join ledg2 In db.ledgers On ledg1.posting_id Equals ledg2.link_posting_id into g _
                From ledg3 In g.defaultIfEmpty() _
                Select ledg3.posting_id()

But my sytax is wrong...

I have included a jpg of the table which should make it clearer.

If someone could point me in the right direction on this it would be helpful.

Kind Regards

Simon
ledger-1.JPG
si2030Asked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi si2030;

Sorry change this line in the query

Join ledg2 In db.ledgers On ledg1.posting_id Equals ledg2.link_posting_id into Group = g _

to this:

Group Join ledg2 In db.ledgers On ledg1.posting_id Equals ledg2.link_posting_id into g = Group _

Fernando
0
 
Fernando SotoRetiredCommented:
Hi si2030;

Try it like this

Dim test3= From ledg1 In db.ledgers _
                Join ledg2 In db.ledgers On ledg1.posting_id Equals ledg2.link_posting_id into Group = g _
                From ledg3 In g.defaultIfEmpty() _
                Select ledg3.posting_id()

Fernando
0
 
si2030Author Commented:
Hi Fernando, yep that works... and it answers the question I asked... I was wondering if you could have a look at the next question I am asking as the sytax didnt reveal what I really wanted once it worked... will try and ask it as succincly as possible.
0
 
si2030Author Commented:
Hi Fernando... just as I posted the solution above I worked out a solution. Here it is...

I am not sure this is the best way to go about it but I combined the two columns into one using an IF statement and then did the order after the Select statement... it worked... surprisingly.

The key part is "orderCol = If(ledger.link_posting_id IsNot Nothing, ledger.link_posting_id, ledger.posting_id), _" and then ordering on this...

Cheers
Simon

Dim test5 = From ledger In db.ledgers _
                                           Where ledger.payment_complete = False And ledger.client_no = clientNo And ((ledger.tran_type = "CI") Or (ledger.tran_type = "PMT" And ledger.acc_id = "2")) _
                                           Select orderCol = If(ledger.link_posting_id IsNot Nothing, ledger.link_posting_id, ledger.posting_id), _
                                           ledger.client_no, _
                                           ledger.acc_id, _
                                         ledger.ref1, _
                                         ledger.tran_type, _
                                         ledger.tran_date, _
                                         ledger.detail, _
                                         ledger.posting_id, _
                                         ledger.link_posting_id, _
                                         amount = -1 * ledger.amount, _
                                         balance = -1 * (If((From ledg2 In db.ledgers Where (ledg2.tran_type = "CI" And ledg2.acc_id = "2" And ledg2.payment_complete = False And ledg2.client_no = ledger.client_no) And _
                                                        (ledg2.posting_id = ledger.posting_id) Select ledg2.amount).Count > 0, _
                                                        (From ledg2 In db.ledgers Where (ledg2.tran_type = "CI" And ledg2.acc_id = "2" And ledg2.payment_complete = False And ledg2.client_no = ledger.client_no) And _
                                                        (ledg2.posting_id = ledger.posting_id) Select ledg2.amount).Sum, _
                                                        (From ledg2 In db.ledgers Where (ledg2.tran_type = "CI" And ledg2.acc_id = "2" And ledg2.payment_complete = False And ledg2.client_no = ledger.client_no) And _
                                                        (ledg2.posting_id = ledger.posting_id) Select ledg2.amount).Count) + _
                                                        If((From ledg3 In db.ledgers Join ledg4 In db.ledgers On ledg3.posting_id Equals ledg4.link_posting_id Where _
                                                        (ledg3.acc_id = "2" And ledg3.payment_complete = False And (ledg3.posting_id = ledger.posting_id) And ledg3.client_no = ledger.client_no) Select ledg4.amount).Count > 0, _
                                                        (From ledg3 In db.ledgers Join ledg4 In db.ledgers On ledg3.posting_id Equals ledg4.link_posting_id Where _
                                                        (ledg3.acc_id = "2" And ledg3.payment_complete = False And (ledg3.posting_id = ledger.posting_id) And ledg3.client_no = ledger.client_no) Select ledg4.amount).Sum, _
                                                        (From ledg3 In db.ledgers Join ledg4 In db.ledgers On ledg3.posting_id Equals ledg4.link_posting_id Where _
                                                        (ledg3.acc_id = "2" And ledg3.payment_complete = False And (ledg3.posting_id = ledger.posting_id) And ledg3.client_no = ledger.client_no) Select ledg4.amount).Count)) _
                                           Order By orderCol
0
 
Fernando SotoRetiredCommented:
Hi si2030;

That looks good to me.

Have a great day;
Fernando
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.