left join with linq how to

Question

I have this linq query:

Dim lnqPrüfendeartikel = From r1 In (From row In dtKonf
                                 Let ARTNR_HZ_ROHNR = row.Field(Of String)("ARTNR-HZ")
                                 Let EMPF_ARTNR = row.Field(Of String)("ARTNR")
                                 Where ARTNR_HZ_ROHNR <> ""
                                 Select ARTNR_HZ_ROHNR, EMPF_ARTNR
                                 Order By ARTNR_HZ_ROHNR).Distinct.Concat( _
                                 (From row In dtKonf
                                 Let ARTNR_HZ_ROHNR = row.Field(Of String)("ROHNR")
                                 Let EMPF_ARTNR = row.Field(Of String)("ARTNR")
                                 Where ARTNR_HZ_ROHNR <> ""
                                 Select ARTNR_HZ_ROHNR, EMPF_ARTNR
                                 Order By ARTNR_HZ_ROHNR).Distinct)
                                 Join r2 In
                                 (From row In dtErlöse
                                 Let ARTNR = row.Field(Of String)("ARTNR")
                                 Let BWA = row.Field(Of String)("BWA")
                                 Let MENGE = row.Field(Of Double)("MENGE")
                                 Let M_EINHEIT = row.Field(Of String)("ME")
                                 Let WERT = row.Field(Of Double)("WERT")
                                 Where BWA = "201"
                                 Select ARTNR, MENGE, M_EINHEIT, WERT) On r1.EMPF_ARTNR Equals r2.ARTNR
                                 Select r1.ARTNR_HZ_ROHNR, r1.EMPF_ARTNR, r2.MENGE, r2.M_EINHEIT, r2.WERT

It works correct. Now, I would like to have all records from r1, not only those, where is a matching record in r2. I also want a left join.

I have searched the net, and I should write after

On r1.EMPF_ARTNR Equals r2.ARTNR

something like this:

into JoinedQuery from r2 in JoinedQuery.DefaultIfEmpty() select new {r1.ARTNR_HZ_ROHNR, r1.EMPF_ARTNR, r2.MENGE, r2.M_EINHEIT, r2.WERT}

But vs not recognises into.

What is here the problem?

score 0 · Accepted Answer · edited May 23 '17 at 12:13

0

The solution you've found is for C#. For VB.NET, you'll need to use:

Group Join r2 In (...) 
On r1.EMPF_ARTNR Equals r2.ARTNR Into JoinedQuery
From r2 In JoinedQuery.DefaultIfEmpty()
...

https://stackoverflow.com/a/13499618/124386

edited May 23 '17 at 12:13

Community

1
1

answered Aug 20 '14 at 14:52

Richard Deeming

29,830
10
79
151

I get the error to this: ' Dim lnqProdsummenmitpreisen = From row In lnqProdsummen Group Join row2 In lnqSollpreise On row.ARTNR_HZ_ROHNR Equals row2.SUCHBEGRIFF Into JoinedQuery() From row2 In JoinedQuery.DefaultIfEmpty' Definition of method 'JoinedQuery' is not accessible in this context. What do I wrong? – derstauner Aug 21 '14 at 17:40
sorry for the code, I don't know, how I write this backtick. – derstauner Aug 21 '14 at 17:46
@derstauner: Try removing the brackets from `Into JoinedQuery()` - it should just be `Into JoinedQuery` with no brackets. – Richard Deeming Aug 21 '14 at 17:47
but vb put it automatic after JoinedQuery – derstauner Aug 21 '14 at 17:53
@derstauner: That sounds like a bug in the IDE; it definitely shouldn't have brackets after it. If you can't take the brackets out, try replacing `JoinedQuery` with a different name. – Richard Deeming Aug 21 '14 at 17:55

left join with linq how to

1 Answers1