Question LINQ - left join to find unmatched records

[stomas]

Hi...I'm trying to execute a left join between 2 datatables that will return all records from the left table without a corresponding value in the right table on the join criteria. As of now I have the following which returns nothing:

Dim Query1 = From exasset In dtExistingAssets _
Group Join asset In dtNewAssets _
On exasset("ACCOUNT_NAME") Equals asset("ACCOUNT_NAME") _
Into results = Group _
From f In results.DefaultIfEmpty _
Where IsDBNull(f) _
Select New With _
{ ...................

I've seen several references to using 'Any' but I wasn't able get the syntax correct. Can anyone please help out? This is something that is really simple to accomplish in SQL but seems a lot more complicated in LINQ...thanks.

 

[Herman]

I would love to get a proper left join linq tutorial because while I generally have no problem with any of the other Linq construct, left joins always bog me down. Just today in a fairly large app that was up to then completely using Linq-Entities, I had to resort to an old style datareader for this one query. I meddled an hour or so before finally copy-pasting the T-SQL I had previously written in SQLMS over my whole Linq query in frustration. Why oh why did MS not include a LeftJoin in Linq is beyond me.

For the record, here is the SQL query in question. If someone manages to convert that to working Linq-Entities, props to you... (I'm still a bit bitter about the whole ordeal... I read many many tutorials, and I understand the concept, but something is fundamentally different between the T-SQL and Linq definitions of LEFT JOIN that I am missing...)

SELECT TOP 1 tc.testcode, tc.[timeout], pe.editioncode, d.dbmscode
FROM TestEditions te
inner join TestConfig tc on te.extestid = tc.id
inner join ProductEditions pe on te.exeditionid = pe.id
inner join dbms d on te.exdbmsid = d.id
inner join languages l on te.exlangid = l.id
inner join products p on pe.exproductid = p.id
-- This is the part that I can't get right in Linq...
left join testlog tl on tc.testcode = tl.testcode
                    and pe.editioncode = tl.editioncode
                    and l.langcode = tl.langcode
                    and d.dbmscode = tl.dbmscode
                    and p.productname = tl.productname
                    -- Specifically this. Linq refuses to
                    -- compare to an absolute value in the
                    -- ON clause.
                    and tl.exbuildid = 8466
-----------------------------------------------------
Where (tl.Success = 0 or tl.success is null)
  and (tl.runningnow = 0 or tl.success is null)
  and tc.inactive = 0
  and p.productname = 'DYNACOM'
  and l.langcode = 'EN'
order by tc.[timeout] asc, tc.testcode asc

And my best guess in Linq:

Dim dc As New AutoTestsEntities

        Dim qNextTest = (From te In dc.TestEditions
                         Join tc In dc.TestConfig On te.ExTestID Equals tc.ID
                         Join pe In dc.ProductEditions On te.ExEditionID Equals pe.ID
                         Join d In dc.DBMS On te.exDbmsId Equals d.ID
                         Join l In dc.Languages On te.ExLangId Equals l.ID
                         Join p In dc.Products On pe.ExProductID Equals p.ID
                         Group Join _tl In dc.TestLog On tc.TestCode Equals _tl.TestCode _
                                                     And pe.EditionCode Equals _tl.EditionCode _
                                                     And l.LangCode Equals _tl.LangCode _
                                                     And d.DbmsCode Equals _tl.DbmsCode _
                                                     And p.ProductName Equals _tl.ProductName _
                                                     And _tl.ExBuildID Equals 8466 _
                         Into tlg = Group
                         From tl In tlg.DefaultIfEmpty()
                         Where (tl.Success = False Or tl.Success Is Nothing) _
                           And (tl.RunningNow = False Or tl.RunningNow Is Nothing) _
                           And tc.Inactive = False _
                           And p.ProductName.ToUpper = "DYNACOM" _
                           And l.LangCode.ToUpper = "EN"
                         Order By tc.Timeout Ascending, tc.TestCode Ascending
                         Select New TCTest With {.TestCode = tc.TestCode,
                                                 .Timeout = tc.Timeout,
                                                 .EditionCode = pe.EditionCode,
                                                 .DbmsCode = d.DbmsCode}).FirstOrDefault