Solved

LINQ outer join query

Posted on 2009-07-01
5
690 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi si2030;

That looks good to me.

Have a great day;
Fernando
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now