Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

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
0
si2030
Asked:
si2030
  • 3
  • 2
1 Solution
 
Fernando SotoCommented:
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
 
Fernando SotoCommented:
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
 
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 SotoCommented:
Hi si2030;

That looks good to me.

Have a great day;
Fernando
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now