use left join in LINQ method synthax

Question

I have two tables: t1 and t3 and I have following sql query:

select t1.*, t3.* from t1 left join t3 on t1.f1=t3.f1

t1 contains 30k records, t3 has only 10. The matching field is f1.

t1 has 5 fields, t3 has 2 fields.

With the above query I get the 30k records + 10 field values also like this:

t1.f1, t1.f2, t1.f3, t1.f4, t1.f5, t3.f1, t3.f2

The records are so, that there is a matching only in case of the first 10 records. So after that, t3.f1 and t3.f2 are empty values.

Now, I would like to write a query in LINQ, wich produces this result:

Dim JoinedResult = LeftTable.GroupJoin(RightTable, Function(LeftTableRow) LeftTableRow(FieldIndexInLeftTable), _
                                                             Function(RightTableRow) RightTableRow(FieldIndexInRightTable), _
                                                             Function(LeftTableRow, RightTableRow) New With {LeftTableRow, RightTableRow.DefaultIfEmpty(Function(Item) IIf(Item Is Nothing, Nothing, Item))})

As you see, I'm trying to tell LINQ, that if any item of the RightTableRow is empty, then I need the field value in this case too, with an empty value.

But vb says: Anonymous type member name can be inferred only from a simple or qualified name with no arguments.

It is important, that the fields of table t3 can be various, so defining an empty value for t3.f1 and t3.f2 is not an option. I need to define an empty value (null) for all fields in t3, if there is no matching record.

It can be, that in t3 there is more matching record to a row in t1, so I must use SelectMany too, but how?

Thanks.

EDIT:

Dim JoinedResult = LeftTable.GroupJoin(RightTable, Function(LeftTableRow) LeftTableRow(FieldIndexInLeftTable), _
                                     Function(RightTableRow) RightTableRow(FieldIndexInRightTable), _
                                     Function(LeftTableRow, RightTableRow) _
                                     New With {LeftTableRow, RightTableRow}). _
                                     SelectMany(Function(Row) _
                                     Row.RightTableRow.DefaultIfEmpty().Select(Function(RightT) _
                                                                                  New With {Row.LeftTableRow, _
                                                                                            Key .rt = RightT.Select(Function(Item) IIf(Item Is Nothing, String.Empty, Item)).ToArray}))

with this the error message disappear, but I get a null exception at runtime, which is obv. results from the fact, that RighT is - except the first 10 records - null.

This is a common question - the answer is to use a GroupJoin combined with DefaultIfEmpty() See http://stackoverflow.com/questions/3404975/left-outer-join-in-linq NB - this was found with a simple search — James S, Jan 02 '15 at 09:33
Yes, this is a common question, but a bit different. In each examples the right table has a fix column number and so it's easy to test the fields of null value. But in my case, the right table may have x columns, in another case y columns. With the edited code (see edit), I don't have the error message anyomore, but I get a nullexception (source cannot be null). — derstauner, Jan 02 '15 at 10:08

score 0 · Answer 1 · edited Jan 02 '15 at 10:23

0

You should look at this link :

http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx

Your code should look to follow :

var query =
   from left in LeftTable
   join right in RightTable on right.FieldIndexInRightTable equals  left.FieldIndexInLeftTable
    into gj
    from subpet in gj.DefaultIfEmpty()
    select new {Your fields };

edited Jan 02 '15 at 10:23

agentpx

1,051
1
8
24

answered Jan 02 '15 at 09:41

Bouam

484
2
10

use left join in LINQ method synthax

1 Answers1