Multiple columns in Linq left outer join

Question

Could somebody help me complete this linq left outer join query so that the second column of the right table (tempData) is contained in the result set even though it may be null?

sortedData = From cs In mbCustomSort
             Order By cs(0)
             Group Join entry In tempData On cs(joinColumn) Equals entry(0) Into Group
             From last In Group.DefaultIfEmpty _
             Select New With {.groupField = cs(joinColumn)}

Sorry, mbcustomsort and tempData are datatables. customsort should read cs — groobie, Oct 31 '12 at 16:28
Does this work: `Select New With {.groupField = cs(joinColumn), .SecondRight = last(1)}`? — Tim Schmelter, Oct 31 '12 at 16:36
It returns "Object reference not set yo instance of an object" — groobie, Oct 31 '12 at 16:43

score 0 · Answer 1 · answered Oct 31 '12 at 16:54

0

Sorry, I write in C#, but how about:

var sortedData = from cs in mbCustomSort orderby cs.JoinColumn
    join entry in tempData on cs.JoinColumn equals entry.OtherJoinColumn into Group
    from subentry in Group.DefaultIfEmpty()
    select new { groupField = cs.JoinColumn };

answered Oct 31 '12 at 16:54

Colin

1,987
3
17
21

Thanks, but doesn't that still only return one column? – groobie Oct 31 '12 at 16:58
What would you like it to return? – Colin Oct 31 '12 at 17:04
How about `select new { groupField = cs.JoinColumn, subentry }` – Colin Oct 31 '12 at 17:09
I want to return the first column of the left table and the second column of the right table (null or not). And, yup, I've tried that one too. – groobie Oct 31 '12 at 17:40
select new { groupField = cs.JoinColumn, subentry } works but the column of the result set is an array and it is confusing to see how to return the elements in it. – groobie Oct 31 '12 at 17:41
your select clause would be like `select new { DesiredOutputNameOfFirstColumn = cs.FirstColumn, DesiredOutputNameOfSecondColumn = subentry.NameOfSecondColumn }` – Colin Oct 31 '12 at 19:53
I tried the following: `sortedData = From customsort In mbCustomSort _ Order By customsort(0) _ Group Join entry In tempDataQuery On customsort(joinColumn) Equals entry(0) Into Group From subentry In Group.DefaultIfEmpty _ Select New With {.groupField = customsort(joinColumn), .total = subentry.total}` but I get a pre-compile error of "total is not a member of System.Data.DataRow" – groobie Oct 31 '12 at 20:23
It means that `subentry.total}` is referring to something that does not exist (subentry does not have a property called `total`). Are you trying to sum up a field in subentry? Try `.total = Group.Sum(columnToSum)` – Colin Nov 02 '12 at 06:50
So groobie, did you find a solution to your question? – Colin Nov 05 '12 at 19:47

Gert Arnold · Answer 2 · 2012-11-01T14:39:41.380

0

(Edited)

The From last In Group turns the outer join into an inner join. This does not happen when you continue with the Group variable:

sortedData = From cs In mbCustomSort
     Order By cs(0)
     Group Join entry In tempData On cs(joinColumn) Equals entry(0) Into Group
     Select New With {.groupField = cs(joinColumn), 
                      .col2 = Group(1).RightColumn }

Change RightColumn by the property of "In-memory Query" object.

edited Nov 01 '12 at 14:39

answered Oct 31 '12 at 21:12

Gert Arnold

105,341
31
202
291

That nearly does it but the contents of the second column in the result set holds an object called "In-memory Query" that contains both columns from the right table - but thanks for getting closer to the solution. Does anyone else have any ideas? – groobie Oct 31 '12 at 22:18
OK, so it might be something like `From row In Group Select row(1).RightColumn`? – Gert Arnold Oct 31 '12 at 23:02
I haven't got enough details to know where to go now. Can you get it working by changing it, helped with intellisense and all? – Gert Arnold Nov 01 '12 at 14:10

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

LINQPad's default processing doesn't allow for item(fieldno), so I used actual field names in data repurposed from here, including adding a new row in Mbc with no corresponding OrderId in Mbtd. This works for me:

From cs In Mbcs _
Order By cs.Catalogid _
Group Join entry In mbtds On cs.OrderId Equals entry.OrderId Into Group _
From last In Group.DefaultIfEmpty _
Select cs.OrderId, last.Ocardtype

And by "works" I mean the row I added appears with a null Ocardtype (as well as another row where Ocardtype was already null).

Multiple columns in Linq left outer join

3 Answers3