Solved

LINQ outer join query

Posted on 2009-07-01
5
691 Views
Last Modified: 2013-11-11
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
Comment
Question by:si2030
  • 3
  • 2
5 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24754067
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
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 24754415
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
 

Author Closing Comment

by:si2030
ID: 31598756
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
 

Author Comment

by:si2030
ID: 24762539
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24764260
Hi si2030;

That looks good to me.

Have a great day;
Fernando
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question