Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

LINQ outer join query

Posted on 2009-07-01
5
692 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 63

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 63

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 63

Expert Comment

by:Fernando Soto
ID: 24764260
Hi si2030;

That looks good to me.

Have a great day;
Fernando
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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