Left Outer Join with static text in the Group Join clause

Question

Hello 

I'm trying to convert the following SQL to a LINQ Query (Note I've changed table names etc. to simplify my explanation):

SELECT * FROM dbo.MainTable 
LEFT OUTER JOIN dbo.Table1 ON MainTable.record_no = Table1.record_no AND MainTable.LinkedTable = 'Table1'
LEFT OUTER JOIN dbo.Table2 ON MainTable.record_no = Table2.record_no AND MainTable.LinkedTable = 'Table2'

I have the following LINQ statement:

Dim qryTest = From MainTable In _db.MainTable
	      Let JoinVals = New With {.Table1 = "Table1", .Table2 = "Table2"} _
	      Group Join Table1 In _db.Table1 On Table1.record_no Equals MainTable.record_no And Table1.compno Equals MainTable.compno And MainTable.LinkedTable Equals JoinVals.Table1 Into jTable1 = Group From Table1 In jTable1.DefaultIfEmpty
	      Group Join Table2 In _db.Table2 On Table2.record_no Equals MainTable.record_no And Table2.compno Equals MainTable.compno And MainTable.LinkedTable Equals JoinVals.Table2 Into jTable2 = Group From Table2 In jTable2.DefaultIfEmpty

The problem being that I can't Join between JoinVals and MainTable on any of the Group Joins because I have to reference the table I'm joining to in it.

Answer 1

Hi,

Please try to use double left outer join: http://msdn.microsoft.com/en-US/library/vstudio/bb397895.aspx